<?php
/*/////////////////////////////////////////////////////////////////////////
//	Esta clase es la responsable de manejar los datos de la tabla client
/////////////////////////////////////////////////////////////////////////*/
class Client{
	var $code;
	var $name;
	var $last_name;
	var $email;
	var $phone;
	var $code_country;	
	var $pass;
	var $type;
	var $status;
	var $bonus;
	var $paymForm;
	var $email_lang;
	
	function Client (){
		$code 		= 0;
		$name 		= '';
		$last_name 	= '';
		$email 		= '';
		$phone 		= '';		
		$pass 		= '';
		$type 		= '';
		$status 	= '';	
		$bonus	 	= '';
		$paymForm	= '';
		$code_country=0;
		$email_lang = 'english';	
	}
	
	
	////////////////////////////////////////////////////////////////////////////////////
	// addClient: Esta funcion es la responsable de agregar un cliente a la B.D
	////////////////////////////////////////////////////////////////////////////////////
	function addClient($data){
		$sql="
		INSERT INTO `client` (
		`cli_code` ,
		`cli_name` ,
		`cli_last_name` ,
		`cli_phone` ,
		`cli_email` ,
		`cou_code` ,
		`state_code` ,
		 cit_name,
		`cli_payment_form` ,
		`cli_password` ,
		`cli_original` ,
		`cli_rename` ,
		`cli_status` ,
		`cli_type` ,
		`cli_date_create`,
		`cli_language_email`
		)
		VALUES (
		NULL ,
		'".$data->name."',
		'".$data->last_name."',
		'".$data->phone."',
		'".$data->email."',
		'".(int)$data->cou_code."',
		'".(int)$data->state_code."', 
		'".$data->cit_name."', 
		'".$data->payment_form."', 
		'".$data->password."', 
		'".$data->original."', 
		'".$data->renam."', 
		'".(int)$data->status."', 
		'".(int)$data->type."',
		'".date("Y-m-d")."',
		'".$data->language_email."');"; 
		
		if(mysql_query($sql))
			return mysql_insert_id();
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
			
			
			
	/**
	* clientList: Esta funcion es la encargada de listar los clientes que existen en el sitio.
	*/
	function clientList($inicio=0 ,$cantidad=0 ,$search='' ,$order_by='', $cli_type='', $buscar_texto = '',$pais = ''){
		
		//include_once("RijndaelHex.php");
	
		//$rijndaelHex = new RijndaelHex();
		
		$where = '';
		if($search != ''){
			
			$where = " WHERE (t1.cli_email like '%".$search."%') ";
			
			if($buscar_texto != '')
			$where .= " AND (t1.cli_name like '%".$buscar_texto."%' 
				   OR t1.cli_last_name like '%".$buscar_texto."%') ";
				   
			if($pais != '')
					$where .= " AND t1.cou_code = ".$pais;
		}
		else
		{
			if($buscar_texto != '')
			{
				$where .= " WHERE (t1.cli_name like '%".$buscar_texto."%' 
					   OR t1.cli_last_name like '%".$buscar_texto."%') ";
					   
				if($pais != '')
					$where .= " AND t1.cou_code = ".$pais;
			}
			else
			{
				if($pais != '')
					$where .= " WHERE t1.cou_code = ".$pais;
			}
					   
		}
		
		if($cli_type!='')
			$cli_type = " AND cli_type=".$cli_type;
		
		$sql = "	
		SELECT t1.cli_code, t1.cli_name, t1.cli_last_name, t1.cli_email, t1.cli_payment_form, t1.cli_rename, t1.cli_status, t1.cli_type, t1.cli_bonus, t1.pais,t1.cou_code, 
			credit_client.pending_credit
		FROM 
			(SELECT cli.cli_code,cli.cou_code, cli.cli_name, cli.cli_last_name, cli.cli_email, cli.cli_payment_form, cli.cli_rename, cli.cli_status, cli.cli_type, cli.cli_bonus, cou.pais
			FROM paises cou
			JOIN client cli ON cou.id = cli.cou_code
			WHERE `cli_status` <>4 ".$cli_type.") t1
		LEFT JOIN 
			(SELECT count( bocl_code ) AS pending_credit, cli_code as id FROM bonus_client WHERE bocl_status = 'Inactivo' GROUP BY cli_code) credit_client
		ON t1.cli_code = credit_client.id ".$where.' '.$order_by;
		
		if(!($inicio==0 && $cantidad==0))
			$sql .= " LIMIT ".$inicio.", ".$cantidad;
		
		if($consult  = mysql_query($sql)){
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult)){
				$data[$i]->code     = $row['cli_code'];
				$data[$i]->name     = $row['cli_name'];
				$data[$i]->last_name= $row['cli_last_name'];
				//$data[$i]->phone    = $row['cli_phone'];
				$data[$i]->email    = $row['cli_email'];
				$data[$i]->payment_form = $row['cli_payment_form'];
				//$data[$i]->password = $row['cli_password'];
				//$data[$i]->original = $row['cli_original'];
				$data[$i]->renam    = $row['cli_rename'];	
				$data[$i]->status   = $row['cli_status'];	
				$data[$i]->type   	= $row['cli_type'];	
				if($data[$i]->type == '2')//es un resellers para traernos el dato del reseller
				{
					$row2 = mysql_fetch_array(mysql_query("SELECT conf_db_url FROM configuration_reseller WHERE cli_code = ".$data[$i]->code));	
					$data[$i]->last_name .= '<br><strong><a href="'.$row2['conf_db_url'].'" target="_blank" style="color:#990000">'.$row2['conf_db_url'].'</a></strong>';
				}
				
				$data[$i]->cou_code = $row['id'];	
				$data[$i]->cou_name = $row['pais'];	
				
				//$data[$i]->conf_db_url   	= $row['conf_db_url'];	 
				
				$pending = $this->getPendingBonusClient($data[$i]->code);				
				
				$data[$i]->cli_bonus_pending = $pending;
				$data[$i]->cli_bonus = $row['cli_bonus'];
				
				$data[$i]->pending_credit = $row['pending_credit'];
				$i++;				
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}	
	
	
	
	
	
	
	/**
	* clientList: Esta funcion es la encargada de listar los clientes que existen en el sitio que pagan en pesos.
	*/
	function clientListPesos(){
	
		$sql = "	
		SELECT t1.cli_code, t1.cli_name, t1.cli_date_create, t1.cli_last_name, t1.cli_email, t1.cli_payment_form, t1.cli_rename, t1.cli_status, t1.cli_type, t1.cli_bonus, t1.pais,t1.cou_code, 
			credit_client.pending_credit
		FROM 
			(SELECT cli.cli_code,cli.cou_code, cli.cli_date_create, cli.cli_name, cli.cli_last_name, cli.cli_email, cli.cli_payment_form, cli.cli_rename, cli.cli_status, cli.cli_type, cli.cli_bonus, cou.pais
			FROM paises cou
			JOIN client cli ON cou.id = cli.cou_code
			WHERE `cli_status` <>4 AND cli.cli_payment_form like '%Pesos Colombianos%') t1
		LEFT JOIN 
			(SELECT count( bocl_code ) AS pending_credit, cli_code as id FROM bonus_client WHERE bocl_status = 'Inactivo' GROUP BY cli_code) credit_client
		ON t1.cli_code = credit_client.id ";
		
		if($consult  = mysql_query($sql)){
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult)){
				$data[$i]->code     = $row['cli_code'];
				$data[$i]->name     = $row['cli_name'];
				$data[$i]->last_name= $row['cli_last_name'];
				$data[$i]->email    = $row['cli_email'];
				$data[$i]->payment_form = $row['cli_payment_form'];
				$data[$i]->renam    = $row['cli_rename'];	
				$data[$i]->status   = $row['cli_status'];	
				$data[$i]->type   	= $row['cli_type'];					
				$data[$i]->cou_code = $row['id'];	
				$data[$i]->cou_name = $row['pais'];	
				$data[$i]->cli_date_create = $row['cli_date_create'];
				
				$pending = $this->getPendingBonusClient($data[$i]->code);				
				
				$data[$i]->cli_bonus_pending = $pending;
				$data[$i]->cli_bonus = $row['cli_bonus'];
				
				$data[$i]->pending_credit = $row['pending_credit'];
				$i++;				
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	
	
	
	
	
	/**
	* clientList: Esta funcion es la encargada de listar los clientes que existen en el sitio que pagan en bolivares.
	*/
	function clientListBolivares(){
	
		$sql = "	
		SELECT t1.cli_code, t1.cli_name, t1.cli_date_create, t1.cli_last_name, t1.cli_email, t1.cli_payment_form, t1.cli_rename, t1.cli_status, t1.cli_type, t1.cli_bonus, t1.pais,t1.cou_code, 
			credit_client.pending_credit
		FROM 
			(SELECT cli.cli_code,cli.cou_code, cli.cli_date_create, cli.cli_name, cli.cli_last_name, cli.cli_email, cli.cli_payment_form, cli.cli_rename, cli.cli_status, cli.cli_type, cli.cli_bonus, cou.pais
			FROM paises cou
			JOIN client cli ON cou.id = cli.cou_code
			WHERE `cli_status` <>4 AND cli.cli_payment_form like '%Bolivares%') t1
		LEFT JOIN 
			(SELECT count( bocl_code ) AS pending_credit, cli_code as id FROM bonus_client WHERE bocl_status = 'Inactivo' GROUP BY cli_code) credit_client
		ON t1.cli_code = credit_client.id ";
		
		if($consult  = mysql_query($sql)){
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult)){
				$data[$i]->code     = $row['cli_code'];
				$data[$i]->name     = $row['cli_name'];
				$data[$i]->last_name= $row['cli_last_name'];
				$data[$i]->email    = $row['cli_email'];
				$data[$i]->payment_form = $row['cli_payment_form'];
				$data[$i]->renam    = $row['cli_rename'];	
				$data[$i]->status   = $row['cli_status'];	
				$data[$i]->type   	= $row['cli_type'];	
				$data[$i]->cli_date_create = $row['cli_date_create'];
				if($data[$i]->type == '2')//es un resellers para traernos el dato del reseller
				{
					$row2 = mysql_fetch_array(mysql_query("SELECT conf_db_url FROM configuration_reseller WHERE cli_code = ".$data[$i]->code));	
					$data[$i]->last_name .= '<br><strong><a href="'.$row2['conf_db_url'].'" target="_blank" style="color:#990000">'.$row2['conf_db_url'].'</a></strong>';
				}
				
				$data[$i]->cou_code = $row['id'];	
				$data[$i]->cou_name = $row['pais'];	
				
				//$data[$i]->conf_db_url   	= $row['conf_db_url'];	 
				
				$pending = $this->getPendingBonusClient($data[$i]->code);				
				
				$data[$i]->cli_bonus_pending = $pending;
				$data[$i]->cli_bonus = $row['cli_bonus'];
				
				$data[$i]->pending_credit = $row['pending_credit'];
				$i++;				
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	
	
	
	
	
	/**
	* clientList: Esta funcion es la encargada de listar los clientes que existen en el sitio que pagan en dolares.
	*/
	function clientListDolares(){
	
		$sql = "	
		SELECT t1.cli_code, t1.cli_name, t1.cli_date_create, t1.cli_last_name, t1.cli_email, t1.cli_payment_form, t1.cli_rename, t1.cli_status, t1.cli_type, t1.cli_bonus, t1.pais,t1.cou_code, 
			credit_client.pending_credit
		FROM 
			(SELECT cli.cli_code,cli.cou_code, cli.cli_date_create, cli.cli_name, cli.cli_last_name, cli.cli_email, cli.cli_payment_form, cli.cli_rename, cli.cli_status, cli.cli_type, cli.cli_bonus, cou.pais
			FROM paises cou
			JOIN client cli ON cou.id = cli.cou_code
			WHERE `cli_status` <>4 AND cli.cli_payment_form like '%Dolares%') t1
		LEFT JOIN 
			(SELECT count( bocl_code ) AS pending_credit, cli_code as id FROM bonus_client WHERE bocl_status = 'Inactivo' GROUP BY cli_code) credit_client
		ON t1.cli_code = credit_client.id ";
		
		if($consult  = mysql_query($sql)){
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult)){
				$data[$i]->code     = $row['cli_code'];
				$data[$i]->name     = $row['cli_name'];
				$data[$i]->last_name= $row['cli_last_name'];
				$data[$i]->email    = $row['cli_email'];
				$data[$i]->payment_form = $row['cli_payment_form'];
				$data[$i]->renam    = $row['cli_rename'];	
				$data[$i]->status   = $row['cli_status'];	
				$data[$i]->type   	= $row['cli_type'];	
				$data[$i]->cli_date_create = $row['cli_date_create'];
				if($data[$i]->type == '2')//es un resellers para traernos el dato del reseller
				{
					$row2 = mysql_fetch_array(mysql_query("SELECT conf_db_url FROM configuration_reseller WHERE cli_code = ".$data[$i]->code));	
					$data[$i]->last_name .= '<br><strong><a href="'.$row2['conf_db_url'].'" target="_blank" style="color:#990000">'.$row2['conf_db_url'].'</a></strong>';
				}
				
				$data[$i]->cou_code = $row['id'];	
				$data[$i]->cou_name = $row['pais'];	
				
				//$data[$i]->conf_db_url   	= $row['conf_db_url'];	 
				
				$pending = $this->getPendingBonusClient($data[$i]->code);				
				
				$data[$i]->cli_bonus_pending = $pending;
				$data[$i]->cli_bonus = $row['cli_bonus'];
				
				$data[$i]->pending_credit = $row['pending_credit'];
				$i++;				
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	
	
	
	
			
	
	///////////////////////////////////////////////////////////////////////////////
	// getDataLiberations: Funcion encargada de traer la informacion de las liberaciones tanto global como de cada una de las liberaciones, imei, clable etc...
	//
	// parameter: seach --> sirve para buscar por nombre, telefono, email o login (puede venir vacio)
	//
	// Retorna: Arreglo con los datos de las liberaciones.
	//
	// Autor: j.h.r
	// Fecha de Creación: 28/11/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	public function getDataLiberations($code)
	{
		$sql      	= "SELECT count(*) AS cant FROM operator_notice AS libimei, liberations AS lib WHERE libimei.lib_code = lib.lib_code AND libimei.libimei_status <> 4 AND cli_code = ".$code;	
		if($consulta 	= mysql_query($sql)){
			$row 		= mysql_fetch_array($consulta);	
			
			$sql2      	= "SELECT count(*) AS cant2 FROM operator_notice AS libimei, liberations AS lib WHERE libimei.lib_code = lib.lib_code AND (libimei_status = 0 OR libimei_status = 2) AND cli_code = ".$code;	
			$consulta2 	= mysql_query($sql2);
			$row2		= mysql_fetch_array($consulta2);	
			
				
			$lib->allLiberations  = $row['cant'];
			$lib->liberationsPend = $row2['cant2'];
				
			return $lib;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////
	// countClients: Esta funcion permite saber en TOTAL cuantos clientes existen en la base de datos, teniendo en cuenta los parametros de entrada
	//
	// parameter: seach --> sirve para buscar por nombre, telefono, email o login (puede venir vacio)
	//
	// Retorna: la cantidad de clientes encontrados
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 05/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	public function countClients($search='',$cli_type='', $buscar_texto = '',$pais='')
	{
		$where = '';
		if($search != ''){
			
			$where .= " WHERE (cli_email like '%".$search."%') ";
		}
		if($buscar_texto != '')
			$where .= " AND (cli_name like '%".$buscar_texto."%' 
				   OR cli_last_name like '%".$buscar_texto."%') ";
				   
		if($pais != '')
			$where .= " AND cou_code = ".$pais;
		
		if($cli_type!='')
			$cli_type = " AND cli_type=".$cli_type;
		
		$sql = "
		SELECT count(*) AS cant FROM client AS cli, paises AS cou 
		WHERE cli.cou_code = cou.id AND `cli_status`<> 4  ".$where.$cli_type.";";
	
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	//////////////////////////////////////////////////////////////////////////////////
	// getAllClients: Esta funcion es la encargada de listar todos los clientes que existen en el sitio.
	///////////////////////////////////////////////////////////////////////////////		
	function getAllClients($cad=''){
		if($cad=='all')
			$sql = "SELECT * FROM client ";
		else
			$sql = "SELECT * FROM client WHERE `cli_status`<> 4 ORDER BY cli_name ASC";
		if($consult  = mysql_query($sql)){
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult)){
				$data[$i]->code     	= $row['cli_code'];
				$data[$i]->name     	= $row['cli_name'];
				$data[$i]->last_name 	= $row['cli_last_name'];
				$data[$i]->cli_email 	= $row['cli_email'];
						
				$i++;
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}		
	
	
	//////////////////////////////////////////////////////////////////////////////////
	// getClientData: Esta funcion es la encargada de obtener los datos de un cliente
	// parameter     : $code-> es el codigo del cliente al que se le van a obtener los datos
	// return        : $data-> son los datos del cliente encontrado
	///////////////////////////////////////////////////////////////////////////////		
	function getClientData($code){
		$sql  = " SELECT t1.*, st.estado FROM (SELECT cli.*, cou.* FROM client cli LEFT JOIN paises cou ON cli.cou_code=cou.id) t1 ";
		$sql .= " LEFT JOIN estados st ON t1.state_code=st.id WHERE t1.cli_code=".(int)$code.";";
		
		if($consult  = mysql_query($sql)) {
		
			$row = mysql_fetch_array($consult);
			
			$data->code    		 = $row['cli_code'];
			$data->name    		 = $row['cli_name'];
			$data->last_name 	 = $row['cli_last_name'];
			$data->phone   		 = $row['cli_phone'];
			$data->email   		 = $row['cli_email'];
			$data->payment_form  = $row['cli_payment_form'];
			$data->password		 = $row['cli_password'];
			$data->original		 = $row['cli_original'];
			$data->renam         = $row['cli_rename'];
			$data->status 		 = $row['cli_status'];
			$data->type   		 = $row['cli_type'];
			$data->creation_date = $row['cli_date_create'];
			$data->email_lang	 = $row['cli_language_email'];
			$data->cli_country_code = $row['cli_country_code'];
			$data->cli_area_code = $row['cli_area_code'];
			$data->cli_confirm_account = $row['cli_confirm_account'];
			
			$data->cou_code 	 = $row['id'];	
			$data->cou_name 	 = $row['pais'];	
			$data->bonus 		 = $row['cli_bonus'];	
			
			$data->state		 = $row['state_code'];		
			$data->name_state	 = $row['estado'];
			
			$data->cit_name		 = $row['cit_name'];
		
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	//////////////////////////////////////////////////////////////////////////////////
	// editClient      : Esta funcion es la encargada de editar los datos de un client
	// parameter     : $new_data -> es el array con los datos a editar
	//////////////////////////////////////////////////////////////////////////////////
	function editClient($new_data)
	{
		$new_file = '';
		if($new_data->original!=''){
			$new_file =
			"`cli_original` = '".$new_data->original."',
			`cli_rename` = '".$new_data->renam."',";
		}
	
		$sql=
		"UPDATE `client` SET 
		`cli_name`     = '".$new_data->name."',
		`cli_last_name`     = '".$new_data->last_name."',
		`cli_phone`    = '".$new_data->phone."',
		`cli_password` = '".$new_data->password."',$new_file
		`cli_status`   = '".$new_data->status."',		
		`cli_language_email`   = '".$new_data->language_email."',
		`cli_type`   = '".$new_data->type."'
		 WHERE `cli_code` =".$new_data->edit_code.";";

	if(mysql_query($sql))
	   		return true;
	else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	//////////////////////////////////////////////////////////////////////////////////
	// deleteClient    : Esta funcion es la encargada de eliminar un cliente
	// parameter       : $code -> es el codigo del cliente a eliminar
	//////////////////////////////////////////////////////////////////////////////////
	function deleteClient($code)
	{
		$sql="UPDATE `client` SET `cli_status` =  '4' WHERE `cli_code` =".$code;				
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	
	///////////////////////////////////////////////////////////////////////////////
	// countLiberationsByClient: Esta funcion permite saber en TOTAL cuantliberaciones ha hecho un cliente
	//
	// parameter: code --> el codigo del cliente del cual va a obtener las liberaciones
	//
	// Retorna: la cantidad de liberaciones encontradas
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 05/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	public function countLiberationsByClient($code , $search='')
	{
		$where = '';		
		if($search != '')
		$where = " AND (serv.ser_name like '%".$search."%' 
		           OR lib.lib_date like '%".$search."%')";
				   		
		$sql = "SELECT count(*) AS cant FROM liberations AS lib , services AS serv
				WHERE serv.ser_code = lib.ser_code AND cli_code = ".$code.$where;
	
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	///////////////////////////////////////////////////////////////////////////////
	// listLiberationsByClient: Esta funcion es la encargada de obtener todas las liberaciones que un cliente ha hecho
	//
	// parameter: seach --> sirve para buscar por nombre, telefono, email o login (puede venir vacio)
	//
	// Retorna: la cantidad de clientes encontrados
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 05/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	function listLiberationsByClient($code,$inicio = 0,$cantidad = 0,$search='',$order_by='')
	{
		$where = '';		
		if($search != '')
		$where = " AND (serv.ser_name like '%".$search."%' 
		           OR lib.lib_date like '%".$search."%')";		
		
		$sql = "SELECT * FROM liberations AS lib , client AS cli , services AS serv
		WHERE (lib.cli_code = cli.cli_code AND serv.ser_code = lib.ser_code AND lib.cli_code = ".$code.$where.")".$order_by." LIMIT ".$inicio.",".$cantidad;		
		
		if($consult  = mysql_query($sql)){		
		
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->lib_code    		 = $row['lib_code'];
				$data[$i]->ser_code    		 = $row['ser_code'];
				$data[$i]->cli_code    		 = $row['cli_code'];
				$data[$i]->lib_date    		 = $row['lib_date'];			
				
				$data[$i]->cli_name    		 = $row['cli_name'];
				$data[$i]->cli_payment_form  = $row['cli_payment_form'];
							
				$data[$i]->ser_name    		 = $row['ser_name'];
				
				$i++;				
			}
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}


	///////////////////////////////////////////////////////////////////////////////
	// countImeisByLiberations: Esta funcion permite saber en TOTAL imeis que se usaron en la liberacion
	//
	// parameter: code --> el codigo de la liberacion de la cual va a obtener los imeis
	//
	// Retorna: la cantidad de imeis encontradas
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 05/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	public function countImeisByLiberations($code , $search='')
	{	
		$where = '';
		if($search != '')
		$where = " AND (libimei_number like '%".$search."%' 
		           OR libimei_date like '%".$search."%')";			
		
		$sql = "SELECT count(*) AS cant FROM operator_notice 
				WHERE lib_code = ".$code.$where;
	
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	///////////////////////////////////////////////////////////////////////////////
	// listImeisByLiberations: Esta funcion es la encargada de obtener todas llos imeis de una liberacion
	//
	// parameter: seach --> sirve para buscar por nombre, telefono, email o login (puede venir vacio)
	//
	// Retorna: la cantidad de clientes encontrados
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 05/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	function listImeisByLiberations($code,$inicio = 0,$cantidad = 0,$search='',$order_by='')
	{
		
		$where = '';		
		if($search != '')
		$where = " AND (lib_I.libimei_number LIKE '%".$search."%'
				  OR lib_I.libimei_date LIKE '%".$search."%')";		
						
		$sql="
		SELECT * FROM operator_notice AS lib_I, liberations AS lib, client AS cli, services AS serv
		WHERE (
		lib_I.lib_code = lib.lib_code
		AND cli.cli_code = lib.cli_code
		AND serv.ser_code = lib.ser_code
		AND lib_I.lib_code =".$code.$where." 
		)".$order_by." LIMIT ".$inicio." , ".$cantidad;
		
		if($consult  = mysql_query($sql)){
				
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->libimei_code  	 = $row['libimei_code'];
				$data[$i]->lib_code    		 = $row['lib_code'];
				$data[$i]->libimei_number	 = $row['libimei_number'];
				$data[$i]->libimei_date 	 = $row['libimei_date'];
				
				$data[$i]->cli_name		 	 = $row['cli_name'];
				$i++;				
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}


	///////////////////////////////////////////////////////////////////////////////
	// getBondsByClient: Esta funcion es la encargada de obtener los Creditos por cliente
	//
	// Retorna: los Creditos encontrados por cliente
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 23/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	function getBondsByClient($code = -1 , $inicio = 0,$cantidad = 0,$search='',$order_by='' , $from_date='' , $to_date = '',$condition='',$bocl_code='',$type=''){
		$where = '';
		if($code != -1)
			$where .= " AND bc.cli_code = ".$code;
		if($type!='')
			$where .= " AND bc.bocl_type = ".$type." AND bc.bocl_status='Activo' ";//para el reporte de Creditos por fecha
			
		if($bocl_code != '')
			$where .= " AND bc.bocl_code = ".$bocl_code;
		
		if($search != '')
			$where .= " AND (bc.bocl_date like '%".$search."%' 
		                OR bc.bocl_lot like '%".$search."%'
						OR bc.bocl_type like '%".$search."%'
						OR bc.bocl_method_of_payment like '%".$search."%'
						OR c.cli_name like '%".$search."%'
						OR c.cli_last_name like '%".$search."%'
						OR bc.bocl_references like '%".$search."%'
						OR bc.bocl_money like '%".$search."%')";	
		
		if($condition!='')
			$where.=' AND('.$condition.') ';
	
		if($from_date != '')
			$where .= " AND (bc.bocl_date >= '".$from_date." 00:00:00') ";
	
		if($to_date != '')
			$where .= " AND (bc.bocl_date <= '".$to_date." 23:59:59') ";						
		
		if($order_by=='')
			$order_by = " ORDER BY bocl_date DESC  ";
		
		$sql = "
				SELECT bc.bocl_code, bc.bocl_date, bc.bocl_lot, bc.bocl_method_of_payment, bc.bocl_money, bc.bocl_type, bc.bocl_references, bc.bocl_currency, bc.bocl_comment,
				bc.bocl_status, bc.bocl_who, bc.bocl_admincomment, bc.file_rename, bc.bocl_commentadmin_private, bc.lib_code, bc.use_code, bc.cli_email, u.use_name
				FROM
				(SELECT bc.bocl_code, bc.bocl_date, bc.bocl_lot, bc.bocl_method_of_payment, bc.bocl_money, bc.bocl_type, bc.bocl_references, bc.bocl_currency, bc.bocl_comment,
				bc.bocl_status, bc.bocl_who, bc.bocl_admincomment, bc.file_rename, bc.bocl_commentadmin_private, bc.lib_code, bc.use_code, c.cli_email 
				FROM bonus_client bc JOIN client c ON bc.cli_code = c.cli_code ".$where.") bc
				LEFT JOIN user u ON bc.use_code=u.use_code ".$order_by." ";
		
		if(!($inicio == 0 && $cantidad == 0))
			$sql .= " LIMIT ".(int) $inicio.", ".(int) $cantidad.";";
		
		if ($consult  = mysql_query($sql)) {	
			$data = array();	
			$i = 0;
			while ($row = mysql_fetch_array($consult)){
				$data[$i]->cli_email	 		= $row['cli_email'];
				$data[$i]->bocl_code	 		= $row['bocl_code'];
				$aux 							= explode(" ",$row['bocl_date']);
				$auxfecha    	 				= cambiarFormatoFecha($aux[0],1);
				$data[$i]->bo_date 				= $auxfecha." ".$aux[1];
				$data[$i]->bonus    			= $row['bocl_lot'];
				$data[$i]->method_of_payment 	= $row['bocl_method_of_payment'];
				$data[$i]->money   				= $row['bocl_money'];
				$data[$i]->type   				= $row['bocl_type'];
				$data[$i]->reference			= $row['bocl_references'];
				$data[$i]->currency				= $row['bocl_currency'];
				$data[$i]->comment				= $row['bocl_comment'];
				$data[$i]->status				= $row['bocl_status'];
				$data[$i]->bocl_who				= $row['bocl_who'];
				$data[$i]->bocl_admincomment	= $row['bocl_admincomment'];
				$data[$i]->file_rename			= $row['file_rename'];
				$data[$i]->commentadmin_private	= $row['bocl_commentadmin_private'];
				$data[$i]->lib_code				= $row['lib_code'];
				$data[$i]->use_code				= $row['use_code'];
				$data[$i]->use_name				= $row['use_name'];
				$i++;
			}
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////
	// countBondsByClient: Esta funcion permite saber en TOTAL Creditos que hay por cliente
	//
	// parameter: code --> el codigo de lal cliente al cual se van a contar los Creditos
	//						cuando viene vacio cuenta los Creditos de todos los clientes.
	//
	// Retorna: la cantidad de Creditos encontrados
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 23/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	public function countBondsByClient($code ='-1', $search='', $from_date='' , $to_date = '',$condition='',$bocl_code='',$type=''){
		$where = '';
		if($code != -1)
			$where .= " AND bc.cli_code = ".$code;
		if($type!='')
			$where .= " AND bc.bocl_type = ".$type." AND bc.bocl_status='Activo' ";//para el reporte de Creditos por fecha
			
		if($bocl_code != '')
			$where .= " AND bc.bocl_code = ".$bocl_code;
		
		if($search != '')
			$where .= " AND (bc.bocl_date like '%".$search."%' 
		                OR bc.bocl_lot like '%".$search."%'
						OR bc.bocl_type like '%".$search."%'
						OR bc.bocl_method_of_payment like '%".$search."%'
						OR c.cli_name like '%".$search."%'
						OR c.cli_last_name like '%".$search."%'
						OR bc.bocl_references like '%".$search."%'
						OR bc.bocl_money like '%".$search."%')";	
		
		if($condition!='')
			$where.=' AND('.$condition.') ';
	
		if($from_date != '')
			$where .= " AND (bc.bocl_date >= '".$from_date." 00:00:00') ";
	
		if($to_date != '')
			$where .= " AND (bc.bocl_date <= '".$to_date." 23:59:59') ";						
		
		if($order_by=='')
			$order_by = " ORDER BY bocl_date DESC  ";
		
		$sql = "SELECT COUNT(*) AS cant FROM bonus_client bc JOIN client c ON bc.cli_code = c.cli_code ".$where;
			
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	//obtiene los datos de un credito.
	function getDataBondsClient($bocl_code){
		$sql = "SELECT * FROM bonus_client bocl JOIN client cli ON bocl.cli_code = cli.cli_code  AND bocl.bocl_code =".(int)$bocl_code.";";		
		if($consult  = mysql_query($sql)){		
			$row = mysql_fetch_array($consult);
					
			$data->cli_name		 		= $row['cli_name'];
			$data->cli_email	 		= $row['cli_email'];
			$data->cli_last_name 		= $row['cli_last_name'];
			$data->cli_code		 		= $row['cli_code'];	
			$data->cou_code		 		= $row['cou_code'];	
			$data->cli_payment_form		= $row['cli_payment_form'];
			
			$data->bocl_code	 		= $row['bocl_code'];
			$aux 						= explode(" ",$row['bocl_date']);
			$data->bocl_date   			= $row['bocl_date'];
			$auxfecha    	 			= cambiarFormatoFecha($aux[0],1);
			$data->bo_date 				= $auxfecha." ".$aux[1];
			$data->bonus    			= $row['bocl_lot'];
			$data->method_of_payment 	= $row['bocl_method_of_payment'];
			$data->money   				= $row['bocl_money'];
			$data->type   				= $row['bocl_type'];
			$data->comment				= $row['bocl_comment'];
			$data->currency				= $row['bocl_currency'];
			$data->reference			= $row['bocl_references'];						
			$data->status				= $row['bocl_status'];
			
			$data->bocl_name   			= $row['bocl_name'];
			$data->bocl_last_name   	= $row['bocl_last_name'];
			$data->bocl_bank			= $row['bocl_bank'];
			$data->bocl_who				= $row['bocl_who'];
			$data->bocl_mtcn			= $row['bocl_mtcn'];						
			$data->bocl_admincomment	= $row['bocl_admincomment'];	
			
			$data->country_name			= $row['country_name'];
			$data->city_name			= $row['city_name'];
			$data->payment_to			= $row['payment_to'];
			$data->email_used_for_payment= $row['email_used_for_payment'];
			$data->file_name			= $row['file_name'];
			$data->file_rename			= $row['file_rename'];
			$data->bocl_from			= $row['bocl_from'];
			$data->bocl_pay				= $row['bocl_pay'];
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	//funcion encargada de aprobar o denegar un credito solicitado por el cliente.
	function approveDenyCredit($data)
	{
		$sql = "UPDATE `bonus_client` SET `bocl_lot` = '".$data->credits."',		
		`bocl_status` = '".$data->status."',
		`bocl_admincomment` = '".$data->admincomment."',		
		`bocl_commentadmin_private` = '".$data->commentadmin_private."',
		`use_code` = '".$data->use_code."'
		WHERE `bocl_code` =".$data->bocl_code;
		
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	
	
	
	///////////////////////////////////////////////////////////////////////////////
	// validateAccount: Esta funcion es la encargada de obtener validar que cuando el cliente intenta loguearse
	///////////////////////////////////////////////////////////////////////////////			
	function validateAccount($email,$pass)
	{
		$sql = "SELECT * FROM client WHERE cli_email = '".$email."' AND cli_password = '".$pass."'";		
		if($consulta = mysql_query($sql)){	
		
			if($row=mysql_fetch_array($consulta))
			{
				$this->code		= $row['cli_code'];
				$this->name		= $row['cli_name'];
				$this->last_name= $row['cli_last_name'];
				$this->email	= $row['cli_email'];
				$this->phone	= $row['cli_phone'];			
				$this->pass		= $row['cli_password'];
				$this->type		= $row['cli_type'];
				$this->status	= $row['cli_status'];
				$this->bonus	= $row['cli_bonus'];
				$this->paymForm	= $row['cli_payment_form'];
				$this->code_country	= $row['cou_code'];
			
				return true;
			}
			else
				return false;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////
	// funcion encargada de verificar si un correo electronico existe o no
	// retorna true si existe, false en caso contrario
	// date: 14.dic.2010
	// author: js
	///////////////////////////////////////////////////////////////////////////////////		
	function issetEmail($email){
		$sql = "SELECT * FROM client WHERE cli_email='".$email."'";
		if($consult= mysql_query($sql)) {
		
			if($row = mysql_fetch_array($consult))
			{
				$this->code		= $row['cli_code'];
				$this->name		= $row['cli_name'];
				$this->last_name= $row['cli_last_name'];
				$this->email	= $row['cli_email'];
				$this->phone	= $row['cli_phone'];			
				$this->pass		= $row['cli_password'];
				$this->type		= $row['cli_type'];
				$this->status	= $row['cli_status'];
				$this->bonus	= $row['cli_bonus'];
				$this->paymForm	= $row['cli_payment_form'];
				$this->code_country	= $row['cou_code'];
				$this->email_lang	= $row['cli_language_email'];
				return true;
			}	
			else
				return false;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	public function clientExist($email)
	{		
		$sql = "SELECT count( cli_email ) AS cant  FROM client WHERE `cli_email` = '".$email."'";		
		if($consulta = mysql_query($sql)) {
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////
	// clientBloq: Funcion encargada de bloquear un cliente.	
	///////////////////////////////////////////////////////////////////////////////
	function clientBloq($code){
		$sql = "UPDATE `client` SET  `cli_status` = 3 WHERE `cli_code` = '".$code."'";
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	function confirmDenyRegistration($code_client,$status){
		$sql = "UPDATE client SET cli_status='".$status."', cli_confirm_account='1' WHERE cli_code= '".(int)$code_client."'";
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	
	//////////////////////////////////////////////////////////////////////////////////
	// listServiceClient : Esta función es la encargada de listar los precios por cliente de cada servicio.
	//////////////////////////////////////////////////////////////////////////////////		
	function listServiceClient($inicio = 0,$cantidad = 0,$search='',$order_by='',$cli_code)
	{	
		$where = '';		
		if($search != '')
			$where = " AND (ser_name like '%".$search."%')";		

		$sql = "
		SELECT clpr_code,serv.ser_code,ser_name, ser_price_purchase,cli.cli_code,serv.ser_status,cli_name,cou_code,cli_bonus,cli_last_name,cli_payment_form, cli_rename,
		clpr_price_col, clpr_price_us,clpr_price_bol 
		FROM `client_price` AS clipr, client AS cli, services AS serv 
		WHERE clipr.`cli_code` = cli.cli_code 
		AND clipr.`ser_code`= serv.ser_code
		AND cli.cli_code=".$cli_code.$where." ORDER BY serv.ser_status DESC LIMIT ".$inicio.",".$cantidad;	
		//die($sql);
		if($consult  = mysql_query($sql)) {
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->clpr_code    	 = $row['clpr_code'];
				$data[$i]->ser_code    		 = $row['ser_code'];
				$data[$i]->ser_name    		 = $row['ser_name'];
				$data[$i]->ser_price_purchase = $row['ser_price_purchase'];
				$data[$i]->ser_status  		 = $row['ser_status'];
				$data[$i]->cli_code   		 = $row['cli_code'];
				$data[$i]->cli_name    		 = $row['cli_name'];
				$data[$i]->cli_pais   		 = $row['cou_code'];
				
				//necesitamos saber el nombre del pais
				$sql2="SELECT pais FROM paises WHERE id = ".$data[$i]->cli_pais ;
				$consult2  = mysql_query($sql2);		
				$row2      = mysql_fetch_array($consult2);
				/////////////////////////////////////////			
				$data[$i]->nom_pais          = $row2['pais'];
				
				$data[$i]->cli_last_name	 = $row['cli_last_name'];
				$data[$i]->cli_bonus		 = $row['cli_bonus'];
				$data[$i]->img_rename   	 = $row['cli_rename'];
				$data[$i]->cli_coin		  	 = $row['cli_payment_form'];
				$data[$i]->clpr_price_col    = $row['clpr_price_col'];
				$data[$i]->clpr_price_us	 = $row['clpr_price_us'];
				$data[$i]->clpr_price_bol	 = $row['clpr_price_bol'];							
				
				$i++;				
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////
	// countServiceClient: Esta funcion es la encargada de contar los servicios por cliente.
	// Autor: j.s.l.p
	///////////////////////////////////////////////////////////////////////////////
	public function countServiceClient($search='',$cli_code)
	{
		$where = '';		
		if($search != '')
			$where = " AND (ser_name like '%".$search."%')";		

		$sql = "
		SELECT count(*) AS cant
		FROM `client_price` AS clipr, client AS cli, services AS serv 
		WHERE clipr.`cli_code` = cli.cli_code
		AND clipr.`ser_code`= serv.ser_code
		AND cli.cli_code=".$cli_code.$where;
		
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	
	
	
	function changePrice($data)
	{
		$sql = "
		UPDATE `client_price` SET `clpr_price_col` = '".$data->clpr_price_col."',
		`clpr_price_us` = '".$data->clpr_price_us."',
		`clpr_price_bol` = '".$data->clpr_price_bol."' 
		WHERE `clpr_code` =".$data->clpr_code;
		
		if(mysql_query($sql))
		  return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}  
	}
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos los paises.
	/////////////////////////////////////////////////////////////////////////////
	function listCountry()
	{
		$sql = "SELECT * FROM `paises`";
		
		if($consult  = mysql_query($sql)){
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->code = $row['id'];
				$data[$i]->name	= $row['pais'];			
				
				$i++;				
			}
			return $data;	
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargada de eliminar la image del cliente
	/////////////////////////////////////////////////////////////////////////////
	function deleteImage($code)
	{
		$sql="
		UPDATE `client` SET `cli_original` = '',
		`cli_rename` = '' WHERE `cli_code` =".(int)$code;
		
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargada de listar todos los estados de un pais.
	/////////////////////////////////////////////////////////////////////////////
	function listStates($code_state)
	{
		$sql = "SELECT * FROM `estados` WHERE relacion =".(int)$code_state;
		
		if($consult  = mysql_query($sql)) {
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->code			= $row['id'];
				$data[$i]->name			= $row['estado'];
				$data[$i]->code_country	= $row['relacion'];
				
				$i++;				
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	
	
	///////////////////////////////////////////////////////////////////////////////
	// listLiberationsImeisByClient: Mostrar las liberaciones de cada imei por cliente, es decir por codigo de cliente.
	//
	// parameter: seach --> sirve para buscar por nombre, telefono, email o login (puede venir vacio)
	//
	// Retorna: la cantidad de clientes encontrados
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 05/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	function listLiberationsImeisByClient($cliCode = '',$inicio = 0,$cantidad = 0,$search='',$order_by='',$status = '',$group='',$service='',$imei='',$createFrom='',
										$createUntil='',$lib='',$code_imei=''){
		
		include_once("RijndaelHex.php");
	
		$rijndael = new RijndaelHex();
		
		$where = '';		
		if($search != '')
			$where = " WHERE (cli_name like '%".$search."%' 
						OR cli_last_name like '%".$search."%'
						OR cli_email like '%".$search."%'
						OR libimei_date like '%".$search."%'
						OR libimei_number like '%".$search."%'
						OR libimei_number_liberation like '%".$search."%'
						OR libimei_date_liberation like '%".$search."%'
						OR libimei_status like '%".$search."%')";
		
		$whereCliCode = '';
		if((int) $cliCode > 0)
			$whereCliCode = " WHERE cli_code = ".(int) $cliCode;
			//$whereCliCode = " WHERE cli_code = ".(int) $cliCode;
		
		if($status != '' && $status != '-3'){
			//0:no liberado , 1:liberado , 2:se trato de liberar pero no se pudo , 3: Not In Data Base.			
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			if($status == -1)//  para la lista de imeis pendientes: No liberados y en verificacion
				$where .= " (libimei_status = 0 OR libimei_status = 2)";	
			elseif($status == -2)// para la lista de imeis liberados y que no existen.
				$where .= " (libimei_status = 1 OR libimei_status = 3)";				
			else
				$where .= " libimei_status = ".(int)$status;
		}			
		elseif($status == -3){// para la todos los estados
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where = " (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3)";
		}
		
		if($group != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " type_service='".$group."' ";
		}
		
		if($service != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " ser_code='".(int)$service."' ";		
		}
		
		if($imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_number like '%".$rijndael->linencrypthex($imei)."%' OR lib_imei like '%".$rijndael->linencrypthex($imei)."%' OR libimei_number like '%".$imei."%' OR lib_imei like '%".$imei."%'";	
		}
		
		if($createFrom != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " libimei_date >= '".$createFrom."' ";
		}
		
		if($createUntil != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_date <= '".$createUntil."' ";	
		}
		
		if($lib != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " lib_code = '".(int)$lib."' ";
		}
		
		if($code_imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libImei_code = '".(int)$code_imei."' ";		
		}
		//echo '<pre>'.$sql.'</pre>';
		
		/*
		file_name
		$sql = "
		SELECT t6.lib_code, t6.ser_code, t6.cli_code, t6.lib_date, t6.lib_country, t6.lib_model, t6.lib_operator, t6.lib_numMep, t6.lib_id, t6.lib_user, t6.lib_serie,
		t6.lib_id_box, t6.lib_description, t6.lib_nick, t6.lib_pass, t6.lib_imei, t6.lib_rm , t6.libimei_code, t6.libimei_number, t6.libimei_date, t6.libimei_date_liberation, 
		t6.libimei_number_liberation, t6.libimei_status, t6.libimei_bonus, t6.libimei_original, t6.libimei_renombre, t6.libimei_verification, t6.libimei_text_cable, 
		t6.API_provider_code, t6.ser_name, t6.img_rename, t6.API_order_id, t6.cli_name, t6.cli_last_name, t6.cli_email, t6.cli_payment_form, t6.cli_rename, t6.cli_status, 
		t6.type_service, t6.mod_name, t6.bra_name, t6.red_name, t6.cou_name, api.api_name
		FROM
		
		(SELECT t5.lib_code, t5.ser_code, t5.cli_code, t5.lib_date, t5.lib_country, t5.lib_model, t5.lib_operator, t5.lib_numMep, t5.lib_id, t5.lib_user, t5.lib_serie,
		t5.lib_id_box, t5.lib_description, t5.lib_nick, t5.lib_pass, t5.lib_imei, t5.lib_rm , t5.libimei_code, t5.libimei_number, t5.libimei_date, t5.libimei_date_liberation, 
		t5.libimei_number_liberation, t5.libimei_status, t5.libimei_bonus, t5.libimei_original, t5.libimei_renombre, t5.libimei_verification, t5.libimei_text_cable, 
		t5.API_provider_code, t5.ser_name, t5.img_rename, t5.API_order_id, t5.cli_name, t5.cli_last_name, t5.cli_email, t5.cli_payment_form, t5.cli_rename, t5.cli_status, 
		t5.type_service, t5.mod_name, t5.bra_name, t5.red_name, c.cou_name
		FROM
		
		(SELECT t4.lib_code, t4.ser_code, t4.cli_code, t4.lib_date, t4.lib_country, t4.lib_model, t4.lib_operator, t4.lib_numMep, t4.lib_id, t4.lib_user, t4.lib_serie,
		t4.lib_id_box, t4.lib_description, t4.lib_nick, t4.lib_pass, t4.lib_imei, t4.lib_rm, t4.libimei_code, t4.libimei_number, t4.libimei_date, t4.libimei_date_liberation, 
		t4.libimei_number_liberation, t4.libimei_status, t4.libimei_bonus, t4.libimei_original, t4.libimei_renombre, t4.libimei_verification, t4.libimei_text_cable, 
		t4.API_provider_code, t4.ser_name, t4.img_rename, t4.API_order_id, t4.cli_name, t4.cli_last_name, t4.cli_email, t4.cli_payment_form, t4.cli_rename, t4.cli_status, 
		t4.type_service, t4.mod_name, t4.bra_name, r.red_name
		FROM
		
		(SELECT t3.lib_code, t3.ser_code, t3.cli_code, t3.lib_date, t3.lib_country, t3.lib_model, t3.lib_operator, t3.lib_numMep, t3.lib_id, t3.lib_user, t3.lib_serie,
		t3.lib_id_box, t3.lib_description, t3.lib_nick, t3.lib_pass, t3.lib_imei, t3.lib_rm, t3.libimei_code, t3.libimei_number, t3.libimei_date, t3.libimei_date_liberation,
		t3.libimei_number_liberation, t3.libimei_status, t3.libimei_bonus, t3.libimei_original, t3.libimei_renombre, t3.libimei_verification, t3.libimei_text_cable, 
		t3.API_provider_code, t3.ser_name, t3.img_rename, t3.API_order_id, t3.cli_name, t3.cli_last_name, t3.cli_email, t3.cli_payment_form, t3.cli_rename, t3.cli_status, 
		t3.type_service, t3.mod_name, b.bra_name
		FROM
		(SELECT t2.lib_code, t2.ser_code, t2.cli_code, t2.lib_date, t2.lib_country, t2.lib_model, t2.lib_operator, t2.lib_numMep, t2.lib_id, t2.lib_user, t2.lib_serie,
		t2.lib_id_box, t2.lib_description, t2.lib_nick, t2.lib_pass, t2.lib_imei, t2.lib_rm, t2.libimei_code, t2.libimei_number, t2.libimei_date, t2.libimei_date_liberation, 
		t2.libimei_number_liberation, t2.libimei_status, t2.libimei_bonus, t2.libimei_original, t2.libimei_renombre, t2.libimei_verification, t2.libimei_text_cable, 
		t2.API_provider_code, t2.ser_name, t2.img_rename, t2.API_order_id, t2.cli_name, t2.cli_last_name, t2.cli_email, t2.cli_payment_form, t2.cli_rename, t2.cli_status, 
		t2.type_service, m.mod_name, m.bra_code
		FROM
		
		(SELECT t1.lib_code, t1.ser_code, t1.cli_code, t1.lib_date, t1.lib_country, t1.lib_model, t1.lib_operator, t1.lib_numMep, t1.lib_id, t1.lib_user, t1.lib_serie,
		t1.lib_id_box, t1.lib_description, t1.lib_nick, t1.lib_pass, t1.lib_imei, t1.lib_rm, t1.libimei_code, t1.libimei_number, t1.libimei_date, t1.libimei_date_liberation, 
		t1.libimei_number_liberation, t1.libimei_status, t1.libimei_bonus, t1.libimei_original, t1.libimei_renombre, t1.libimei_verification, t1.libimei_text_cable, 
		t1.API_provider_code, t1.ser_name, t1.img_rename, t1.API_order_id, t1.cli_name, t1.cli_last_name, t1.cli_email, t1.cli_payment_form, t1.cli_rename, t1.cli_status, 
		st.type_service
		FROM
		
		(SELECT t.lib_code, t.ser_code, t.cli_code, t.lib_date, t.lib_country, t.lib_model, t.lib_operator, t.lib_numMep, t.lib_id, t.lib_user, t.lib_serie, t.lib_id_box,
		t.lib_description, t.lib_nick, t.lib_pass, t.lib_imei, t.lib_rm, t.libimei_code, t.libimei_number, t.libimei_date, t.libimei_date_liberation, 
		t.libimei_number_liberation, t.libimei_status, t.libimei_bonus, t.libimei_original, t.libimei_renombre, t.libimei_verification, t.libimei_text_cable, 
		t.API_provider_code, t.ser_name, t.img_rename, t.ser_type_code, t.API_order_id, c.cli_name, c.cli_last_name, c.cli_email, c.cli_payment_form, c.cli_rename, c.cli_status
		FROM
				
		(SELECT t0.lib_code, t0.ser_code, t0.cli_code, t0.lib_date, t0.lib_country, t0.lib_model, t0.lib_operator, t0.lib_numMep, t0.lib_id, t0.lib_user, t0.lib_serie,
		t0.lib_id_box, t0.lib_description, t0.lib_nick, t0.lib_pass, t0.lib_imei, t0.lib_rm, t0.libimei_code, t0.libimei_number, t0.libimei_date, t0.libimei_date_liberation, 
		t0.libimei_number_liberation, t0.libimei_status, t0.libimei_bonus, t0.libimei_original, t0.libimei_renombre, t0.libimei_verification, t0.libimei_text_cable, 
		t0.API_provider_code, t0.API_order_id, s.ser_name, s.img_rename, s.ser_type_code
		FROM
		
		(SELECT l.lib_code, l.ser_code, l.cli_code, l.lib_date, l.lib_country, l.lib_model, l.lib_operator, l.lib_numMep, l.lib_id, l.lib_user, l.lib_serie, l.lib_id_box,
		l.lib_description, l.lib_nick, l.lib_pass, l.lib_imei, l.lib_rm , li.libimei_code, li.libimei_number, li.libimei_date, li.libimei_date_liberation,
		libimei_number_liberation, li.libimei_status, li.libimei_bonus, li.libimei_original, li.libimei_renombre, li.libimei_verification, li.libimei_text_cable, 
		li.API_provider_code, li.API_order_id
		FROM liberations l JOIN operator_notice li 
		ON l.lib_code=li.lib_code ".$whereCliCode.") t0
		JOIN services s ON s.ser_code = t0.ser_code) t
		JOIN client c ON t.cli_code = c.cli_code) t1
		JOIN service_type st ON t1.ser_type_code = st.ser_type_code) t2
		LEFT JOIN model m ON t2.lib_model = m.mod_code) t3
		LEFT JOIN brand b ON t3.bra_code = b.bra_code) t4
		LEFT JOIN red r ON t4.lib_operator = r.red_id) t5
		LEFT JOIN country c ON t5.lib_country = c.cou_code) t6
		LEFT JOIN api ON t6.API_provider_code = api.api_code ".$where.$order_by;*/
		
		$sql = "
		SELECT t2.lib_code, t2.ser_code, t2.cli_code, t2.lib_imei, t2.libimei_code, t2.libimei_number, t2.libimei_date, t2.libimei_date_liberation, 
		t2.libimei_number_liberation, t2.libimei_status, t2.libimei_bonus, t2.libimei_original, t2.libimei_renombre, t2.libimei_verification, 
		t2.API_provider_code, t2.ser_name, t2.img_rename, t2.API_order_id, t2.cli_name, t2.cli_last_name, t2.cli_email, t2.cli_payment_form,
		t2.type_service, api.api_name
		FROM		
		(SELECT t1.lib_code, t1.ser_code, t1.cli_code, t1.lib_imei,  t1.libimei_code, t1.libimei_number, t1.libimei_date, t1.libimei_date_liberation, 
		t1.libimei_number_liberation, t1.libimei_status, t1.libimei_bonus, t1.libimei_original, t1.libimei_renombre, t1.libimei_verification, 
		t1.API_provider_code, t1.ser_name, t1.img_rename, t1.API_order_id, t1.cli_name, t1.cli_last_name, t1.cli_email, t1.cli_payment_form,
		st.type_service
		FROM
		
		(SELECT t.lib_code, t.ser_code, t.cli_code, t.lib_imei, t.libimei_code, t.libimei_number, t.libimei_date, t.libimei_date_liberation, 
		t.libimei_number_liberation, t.libimei_status, t.libimei_bonus, t.libimei_original, t.libimei_renombre, t.libimei_verification, 
		t.API_provider_code, t.ser_name, t.img_rename, t.ser_type_code, t.API_order_id, c.cli_name, c.cli_last_name, c.cli_email, c.cli_payment_form
		FROM
				
		(SELECT t0.lib_code, t0.ser_code, t0.cli_code, t0.lib_imei, t0.libimei_code, t0.libimei_number, t0.libimei_date, t0.libimei_date_liberation, 
		t0.libimei_number_liberation, t0.libimei_status, t0.libimei_bonus, t0.libimei_original, t0.libimei_renombre, t0.libimei_verification, 
		t0.API_provider_code, t0.API_order_id, s.ser_name, s.img_rename, s.ser_type_code
		FROM
		
		(SELECT l.lib_code, l.ser_code, l.cli_code, l.lib_imei, li.libimei_code, li.libimei_number, li.libimei_date, li.libimei_date_liberation,
		li.libimei_number_liberation, li.libimei_status, li.libimei_bonus, li.libimei_original, li.libimei_renombre, li.libimei_verification, 
		li.API_provider_code, li.API_order_id
		FROM liberations l JOIN operator_notice li 
		ON l.lib_code=li.lib_code ".$whereCliCode.") t0
		JOIN services s ON s.ser_code = t0.ser_code) t
		JOIN client c ON t.cli_code = c.cli_code) t1
		JOIN service_type st ON t1.ser_type_code = st.ser_type_code) t2
		LEFT JOIN api ON t2.API_provider_code = api.api_code ".$where.$order_by;
		
		/*echo $sql = "
		SELECT libe.lib_code, libe.ser_code, libe.cli_code, libe.lib_imei,  liim.libimei_code, liim.libimei_number, liim.libimei_date, liim.libimei_date_liberation, 
		liim.libimei_number_liberation, liim.libimei_status, liim.libimei_bonus, liim.libimei_original, liim.libimei_renombre, liim.libimei_verification, 
		liim.API_provider_code, ser.ser_name, ser.img_rename, API_order_id, cli.cli_name, cli.cli_last_name, cli.cli_email, cli.cli_payment_form,
		type_service, api_name 
		FROM 
		operator_notice AS liim, liberations AS libe, services AS ser, client AS cli, service_type AS sety, api
		WHERE
		liim.lib_code = libe.lib_code AND libe.ser_code = ser.ser_code AND cli.cli_code = libe.cli_code AND ser.ser_type_code = sety.ser_type_code AND  liim.API_provider_code = api.api_code ".$whereCliCode.$where.$order_by;*/
		
		if(!($inicio == 0 && $cantidad == 0))
			$sql .= ' LIMIT '.(int) $inicio.', '.(int) $cantidad.';';
		//echo $sql;
		if($consult  = mysql_query($sql)){
			$data =array();
			$i = 0;
			$new = 0;//cuando no tenemos liberaciones nuevas en ese rango de horas.
			
			while($row = mysql_fetch_array($consult)){
				$data[$i]->ser_code = $row['ser_code'];
				$data[$i]->cli_code = $row['cli_code'];
				$data[$i]->cli_name = $row['cli_name'];
				$data[$i]->cli_last_name = $row['cli_last_name'];
				$data[$i]->cli_email = $row['cli_email'];
				$data[$i]->cli_payment_form = $row['cli_payment_form'];
				$data[$i]->ser_name = $row['ser_name'];
				$data[$i]->ser_img_rename= $row['img_rename'];
				$data[$i]->lib_imei = $row['lib_imei'];
				$data[$i]->libImei_code = $row['libimei_code'];
				$data[$i]->libImei_number = $row['libimei_number'];
				$data[$i]->libImei_date = $row['libimei_date'];
				$data[$i]->libImei_date_lib = $row['libimei_date_liberation'];
				$data[$i]->libImei_num_lib = $row['libimei_number_liberation'];
				$data[$i]->libImei_status = $row['libimei_status'];
				$data[$i]->libimei_bonus = $row['libimei_bonus'];
				$data[$i]->type_service = $row['type_service'];
				$data[$i]->libimei_original = $row['libimei_original'];
				$data[$i]->libimei_renombre = $row['libimei_renombre'];
				$data[$i]->libimei_verification = $row['libimei_verification'];
				
				//--------------------------------------------------------------
				
				$data[$i]->API_provider_code = $row["API_provider_code"];
				$data[$i]->API_order_id = $row["API_order_id"];
				$data[$i]->api_name = $row["api_name"];
				
				$i++;				
			}
			//die(print_r($data));
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	///////////////////////////////////////////////////////////////////////////////
	// listLiberationsImeisByClient: Mostrar las liberaciones de cada imei por cliente, es decir por codigo de cliente.
	//
	// parameter: seach --> sirve para buscar por nombre, telefono, email o login (puede venir vacio)
	//
	// Retorna: la cantidad de clientes encontrados
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 05/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	function listLiberationsImeisByClientOptimized($cliCode = '',$inicio = 0,$cantidad = 0,$search='',$order_by='',$status = '',$group='',$service='',$imei='',$createFrom='',
										$createUntil='',$lib='',$code_imei=''){
		
		include_once("RijndaelHex.php");
	
		$rijndael = new RijndaelHex();
		
		$where = '';		
		if($search != '')
			$where = " WHERE (cli_name like '%".$search."%' 
						OR cli_last_name like '%".$search."%'
						OR cli_email like '%".$search."%'
						OR libimei_date like '%".$search."%'
						OR libimei_number like '%".$search."%'
						OR libimei_number_liberation like '%".$search."%'
						OR libimei_date_liberation like '%".$search."%'
						OR libimei_status like '%".$search."%')";
		
		$whereCliCode = '';
		if((int) $cliCode > 0)
			$whereCliCode = " AND l.cli_code = ".(int) $cliCode;
			//$whereCliCode = " WHERE cli_code = ".(int) $cliCode;
		
		if($status != '' && $status != '-3'){
			//0:no liberado , 1:liberado , 2:se trato de liberar pero no se pudo , 3: Not In Data Base.			
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			if($status == -1)//  para la lista de imeis pendientes: No liberados y en verificacion
				$where .= " (libimei_status = 0 OR libimei_status = 2)";	
			elseif($status == -2)// para la lista de imeis liberados y que no existen.
				$where .= " (libimei_status = 1 OR libimei_status = 3)";				
			else
				$where .= " libimei_status = ".(int)$status;
		}			
		elseif($status == -3){// para la todos los estados
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where = " (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3)";
		}
		
		if($group != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " type_service='".$group."' ";
		}
		
		if($service != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " ser_code='".(int)$service."' ";		
		}
		
		if($imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_number like '%".$rijndael->linencrypthex($imei)."%' OR lib_imei like '%".$rijndael->linencrypthex($imei)."%' OR libimei_number like '%".$imei."%' OR lib_imei like '%".$imei."%'";	
		}
		
		if($createFrom != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " libimei_date >= '".$createFrom."' ";
		}
		
		if($createUntil != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_date <= '".$createUntil."' ";	
		}
		
		if($lib != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " lib_code = '".(int)$lib."' ";
		}
		
		if($code_imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libImei_code = '".(int)$code_imei."' ";		
		}
		
		$sql = "
		select api_name,t2.API_order_id,t2.API_provider_code,t2.lib_imei, t2.cli_name, t2.ser_code, t2.cli_code, t2.cli_last_name, t2.cli_email, t2.cli_payment_form,t2.ser_name, t2.ser_price_purchase, t2.img_rename, t2.libimei_code, t2.libimei_number, t2.libimei_date, t2.libimei_date_liberation, t2.libimei_number_liberation, t2.libimei_status, t2.libimei_bonus, t2.type_service, t2.libimei_original, t2.libimei_renombre, t2.libimei_verification FROm (select l.lib_imei, cli_name, s.ser_code, c.cli_code, cli_last_name, cli_email, cli_payment_form,ser_name, ser_price_purchase, s.img_rename, op.libimei_code, libimei_number, libimei_date, libimei_date_liberation, libimei_number_liberation, libimei_status, libimei_bonus, ts.type_service, libimei_original, libimei_renombre, libimei_verification, op.API_provider_code,op.API_order_id FROM operator_notice op, liberations l, client c, services s, service_type ts WHERE op.lib_code=l.lib_code AND c.cli_code = l.cli_code AND s.ser_code = l.ser_code AND ts.ser_type_code = s.ser_type_code AND (libimei_status = 0 OR libimei_status = 2) ".$whereCliCode.")t2 LEFT JOIN api ON t2.API_provider_code = api.api_code ".$where.$order_by;
		
		if(!($inicio == 0 && $cantidad == 0))
			$sql .= ' LIMIT '.(int) $inicio.', '.(int) $cantidad.';';
		//echo $sql;
		if($consult  = mysql_query($sql)){
			$data =array();
			$i = 0;
			$new = 0;//cuando no tenemos liberaciones nuevas en ese rango de horas.
			
			while($row = mysql_fetch_array($consult)){
				$data[$i]->ser_code = $row['ser_code'];
				$data[$i]->cli_code = $row['cli_code'];
				$data[$i]->cli_name = $row['cli_name'];
				$data[$i]->cli_last_name = $row['cli_last_name'];
				$data[$i]->cli_email = $row['cli_email'];
				$data[$i]->cli_payment_form = $row['cli_payment_form'];
				$data[$i]->ser_name = $row['ser_name'];
				$data[$i]->ser_img_rename= $row['img_rename'];
				$data[$i]->lib_imei = $row['lib_imei'];
				$data[$i]->libImei_code = $row['libimei_code'];
				$data[$i]->libImei_number = $row['libimei_number'];
				$data[$i]->libImei_date = $row['libimei_date'];
				$data[$i]->libImei_date_lib = $row['libimei_date_liberation'];
				$data[$i]->libImei_num_lib = $row['libimei_number_liberation'];
				$data[$i]->libImei_status = $row['libimei_status'];
				$data[$i]->libimei_bonus = $row['libimei_bonus'];
				$data[$i]->type_service = $row['type_service'];
				$data[$i]->libimei_original = $row['libimei_original'];
				$data[$i]->libimei_renombre = $row['libimei_renombre'];
				$data[$i]->libimei_verification = $row['libimei_verification'];
				$data[$i]->ser_price_purchase = $row['ser_price_purchase'];
				
				//--------------------------------------------------------------
				
				$data[$i]->API_provider_code = $row["API_provider_code"];
				$data[$i]->API_order_id = $row["API_order_id"];
				$data[$i]->api_name = $row["api_name"];
				
				$i++;				
			}
			//die(print_r($data));
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	function listLiberationsImeisByClientOptimized2($cliCode = '',$inicio = 0,$cantidad = 0,$search='',$order_by='',$status = '',$group='',$service='',$imei='',$createFrom='',
										$createUntil='',$lib='',$code_imei=''){
		
		include_once("RijndaelHex.php");
	
		$rijndael = new RijndaelHex();
		
		$where = '';		
		if($search != '')
			$where = " WHERE (cli_name like '%".$search."%' 
						OR cli_last_name like '%".$search."%'
						OR cli_email like '%".$search."%'
						OR libimei_date like '%".$search."%'
						OR libimei_number like '%".$search."%'
						OR libimei_number_liberation like '%".$search."%'
						OR libimei_date_liberation like '%".$search."%'
						OR libimei_status like '%".$search."%')";
		
		$whereCliCode = '';
		if((int) $cliCode > 0)
			$whereCliCode = " AND l.cli_code = ".(int) $cliCode;
			//$whereCliCode = " WHERE cli_code = ".(int) $cliCode;
		
		if($status != '' && $status != '-3'){
			//0:no liberado , 1:liberado , 2:se trato de liberar pero no se pudo , 3: Not In Data Base.			
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			if($status == -1)//  para la lista de imeis pendientes: No liberados y en verificacion
				$where .= " (libimei_status = 0 OR libimei_status = 2)";	
			elseif($status == -2)// para la lista de imeis liberados y que no existen.
				$where .= " (libimei_status = 1 OR libimei_status = 3)";				
			else
				$where .= " libimei_status = ".(int)$status;
		}			
		elseif($status == -3){// para la todos los estados
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where = " (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3)";
		}
		
		if($group != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " type_service='".$group."' ";
		}
		
		if($service != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " ser_code='".(int)$service."' ";		
		}
		
		if($imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_number like '%".$rijndael->linencrypthex($imei)."%' OR lib_imei like '%".$rijndael->linencrypthex($imei)."%' OR libimei_number like '%".$imei."%' OR lib_imei like '%".$imei."%'";	
		}
		
		if($createFrom != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " libimei_date >= '".$createFrom."' ";
		}
		
		if($createUntil != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_date <= '".$createUntil."' ";	
		}
		
		if($lib != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " lib_code = '".(int)$lib."' ";
		}
		
		if($code_imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libImei_code = '".(int)$code_imei."' ";		
		}
		
		$sql = "
		select t2.API_order_id,t2.API_provider_code,t2.lib_imei, t2.cli_name, t2.ser_code, t2.cli_code, t2.cli_last_name, t2.cli_email, t2.cli_payment_form,t2.ser_name, t2.img_rename, t2.libimei_code, t2.libimei_number, t2.libimei_date, t2.libimei_date_liberation, t2.libimei_number_liberation, t2.libimei_status, t2.libimei_bonus, t2.type_service, t2.libimei_original, t2.libimei_renombre, t2.libimei_verification FROm (select l.lib_imei, cli_name, s.ser_code, c.cli_code, cli_last_name, cli_email, cli_payment_form,ser_name, s.img_rename, op.libimei_code, libimei_number, libimei_date, libimei_date_liberation, libimei_number_liberation, libimei_status, libimei_bonus, ts.type_service, libimei_original, libimei_renombre, libimei_verification, op.API_provider_code,op.API_order_id FROM operator_notice op, liberations l, client c, services s, service_type ts WHERE op.lib_code=l.lib_code AND c.cli_code = l.cli_code AND s.ser_code = l.ser_code AND ts.ser_type_code = s.ser_type_code AND (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3) ".$whereCliCode.")t2 LEFT JOIN api ON t2.API_provider_code = api.api_code ".$where.$order_by;
		
		if(!($inicio == 0 && $cantidad == 0))
			$sql .= ' LIMIT '.(int) $inicio.', '.(int) $cantidad.';';
		//echo $sql;
		if($consult  = mysql_query($sql)){
			$data =array();
			$i = 0;
			$new = 0;//cuando no tenemos liberaciones nuevas en ese rango de horas.
			
			while($row = mysql_fetch_array($consult)){
				$data[$i]->ser_code = $row['ser_code'];
				$data[$i]->cli_code = $row['cli_code'];
				$data[$i]->cli_name = $row['cli_name'];
				$data[$i]->cli_last_name = $row['cli_last_name'];
				$data[$i]->cli_email = $row['cli_email'];
				$data[$i]->cli_payment_form = $row['cli_payment_form'];
				$data[$i]->ser_name = $row['ser_name'];
				$data[$i]->ser_img_rename= $row['img_rename'];
				$data[$i]->lib_imei = $row['lib_imei'];
				$data[$i]->libImei_code = $row['libimei_code'];
				$data[$i]->libImei_number = $row['libimei_number'];
				$data[$i]->libImei_date = $row['libimei_date'];
				$data[$i]->libImei_date_lib = $row['libimei_date_liberation'];
				$data[$i]->libImei_num_lib = $row['libimei_number_liberation'];
				$data[$i]->libImei_status = $row['libimei_status'];
				$data[$i]->libimei_bonus = $row['libimei_bonus'];
				$data[$i]->type_service = $row['type_service'];
				$data[$i]->libimei_original = $row['libimei_original'];
				$data[$i]->libimei_renombre = $row['libimei_renombre'];
				$data[$i]->libimei_verification = $row['libimei_verification'];
				
				//--------------------------------------------------------------
				
				$data[$i]->API_provider_code = $row["API_provider_code"];
				$data[$i]->API_order_id = $row["API_order_id"];
				$data[$i]->api_name = $row["api_name"];
				
				$i++;				
			}
			//die(print_r($data));
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	function listLiberationsImeisByClientPublic($cliCode = '',$inicio = 0,$cantidad = 0,$search='',$order_by='',$status = '',$group='',$service='',$imei='',$createFrom='',
										$createUntil='',$lib='',$code_imei=''){
		
		//include_once("RijndaelHex.php");
	
		$rijndael = new RijndaelHex();
		
		$where = '';		
		if($search != '')
			$where = " WHERE (cli_name like '%".$search."%' 
						OR cli_last_name like '%".$search."%'
						OR cli_email like '%".$search."%'
						OR libimei_date like '%".$search."%'
						OR libimei_number like '%".$search."%'
						OR libimei_number_liberation like '%".$search."%'
						OR libimei_date_liberation like '%".$search."%'
						OR libimei_status like '%".$search."%')";
		
		$whereCliCode = '';
		if((int) $cliCode > 0)
			$whereCliCode = " WHERE cli_code = ".(int) $cliCode;
			//$whereCliCode = " WHERE cli_code = ".(int) $cliCode;
		
		if($status != '' && $status != '-3'){
			//0:no liberado , 1:liberado , 2:se trato de liberar pero no se pudo , 3: Not In Data Base.			
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			if($status == -1)//  para la lista de imeis pendientes: No liberados y en verificacion
				$where .= " (libimei_status = 0 OR libimei_status = 2)";	
			elseif($status == -2)// para la lista de imeis liberados y que no existen.
				$where .= " (libimei_status = 1 OR libimei_status = 3)";				
			else
				$where .= " libimei_status = ".(int)$status;
		}			
		elseif($status == -3){// para la todos los estados
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where = " (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3)";
		}
		
		if($group != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " type_service='".$group."' ";
		}
		
		if($service != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " ser_code='".(int)$service."' ";		
		}
		
		if($imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_number like '%".$imei."%' OR lib_imei like '%".$imei."%' ";	
		}
		
		if($createFrom != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " libimei_date >= '".$createFrom."' ";
		}
		
		if($createUntil != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_date <= '".$createUntil."' ";	
		}
		
		if($lib != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " lib_code = '".(int)$lib."' ";
		}
		
		if($code_imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libImei_code = '".(int)$code_imei."' ";		
		}
		//echo '<pre>'.$sql.'</pre>';
		
		/*
		file_name
		$sql = "
		SELECT t6.lib_code, t6.ser_code, t6.cli_code, t6.lib_date, t6.lib_country, t6.lib_model, t6.lib_operator, t6.lib_numMep, t6.lib_id, t6.lib_user, t6.lib_serie,
		t6.lib_id_box, t6.lib_description, t6.lib_nick, t6.lib_pass, t6.lib_imei, t6.lib_rm , t6.libimei_code, t6.libimei_number, t6.libimei_date, t6.libimei_date_liberation, 
		t6.libimei_number_liberation, t6.libimei_status, t6.libimei_bonus, t6.libimei_original, t6.libimei_renombre, t6.libimei_verification, t6.libimei_text_cable, 
		t6.API_provider_code, t6.ser_name, t6.img_rename, t6.API_order_id, t6.cli_name, t6.cli_last_name, t6.cli_email, t6.cli_payment_form, t6.cli_rename, t6.cli_status, 
		t6.type_service, t6.mod_name, t6.bra_name, t6.red_name, t6.cou_name, api.api_name
		FROM
		
		(SELECT t5.lib_code, t5.ser_code, t5.cli_code, t5.lib_date, t5.lib_country, t5.lib_model, t5.lib_operator, t5.lib_numMep, t5.lib_id, t5.lib_user, t5.lib_serie,
		t5.lib_id_box, t5.lib_description, t5.lib_nick, t5.lib_pass, t5.lib_imei, t5.lib_rm , t5.libimei_code, t5.libimei_number, t5.libimei_date, t5.libimei_date_liberation, 
		t5.libimei_number_liberation, t5.libimei_status, t5.libimei_bonus, t5.libimei_original, t5.libimei_renombre, t5.libimei_verification, t5.libimei_text_cable, 
		t5.API_provider_code, t5.ser_name, t5.img_rename, t5.API_order_id, t5.cli_name, t5.cli_last_name, t5.cli_email, t5.cli_payment_form, t5.cli_rename, t5.cli_status, 
		t5.type_service, t5.mod_name, t5.bra_name, t5.red_name, c.cou_name
		FROM
		
		(SELECT t4.lib_code, t4.ser_code, t4.cli_code, t4.lib_date, t4.lib_country, t4.lib_model, t4.lib_operator, t4.lib_numMep, t4.lib_id, t4.lib_user, t4.lib_serie,
		t4.lib_id_box, t4.lib_description, t4.lib_nick, t4.lib_pass, t4.lib_imei, t4.lib_rm, t4.libimei_code, t4.libimei_number, t4.libimei_date, t4.libimei_date_liberation, 
		t4.libimei_number_liberation, t4.libimei_status, t4.libimei_bonus, t4.libimei_original, t4.libimei_renombre, t4.libimei_verification, t4.libimei_text_cable, 
		t4.API_provider_code, t4.ser_name, t4.img_rename, t4.API_order_id, t4.cli_name, t4.cli_last_name, t4.cli_email, t4.cli_payment_form, t4.cli_rename, t4.cli_status, 
		t4.type_service, t4.mod_name, t4.bra_name, r.red_name
		FROM
		
		(SELECT t3.lib_code, t3.ser_code, t3.cli_code, t3.lib_date, t3.lib_country, t3.lib_model, t3.lib_operator, t3.lib_numMep, t3.lib_id, t3.lib_user, t3.lib_serie,
		t3.lib_id_box, t3.lib_description, t3.lib_nick, t3.lib_pass, t3.lib_imei, t3.lib_rm, t3.libimei_code, t3.libimei_number, t3.libimei_date, t3.libimei_date_liberation,
		t3.libimei_number_liberation, t3.libimei_status, t3.libimei_bonus, t3.libimei_original, t3.libimei_renombre, t3.libimei_verification, t3.libimei_text_cable, 
		t3.API_provider_code, t3.ser_name, t3.img_rename, t3.API_order_id, t3.cli_name, t3.cli_last_name, t3.cli_email, t3.cli_payment_form, t3.cli_rename, t3.cli_status, 
		t3.type_service, t3.mod_name, b.bra_name
		FROM
		(SELECT t2.lib_code, t2.ser_code, t2.cli_code, t2.lib_date, t2.lib_country, t2.lib_model, t2.lib_operator, t2.lib_numMep, t2.lib_id, t2.lib_user, t2.lib_serie,
		t2.lib_id_box, t2.lib_description, t2.lib_nick, t2.lib_pass, t2.lib_imei, t2.lib_rm, t2.libimei_code, t2.libimei_number, t2.libimei_date, t2.libimei_date_liberation, 
		t2.libimei_number_liberation, t2.libimei_status, t2.libimei_bonus, t2.libimei_original, t2.libimei_renombre, t2.libimei_verification, t2.libimei_text_cable, 
		t2.API_provider_code, t2.ser_name, t2.img_rename, t2.API_order_id, t2.cli_name, t2.cli_last_name, t2.cli_email, t2.cli_payment_form, t2.cli_rename, t2.cli_status, 
		t2.type_service, m.mod_name, m.bra_code
		FROM
		
		(SELECT t1.lib_code, t1.ser_code, t1.cli_code, t1.lib_date, t1.lib_country, t1.lib_model, t1.lib_operator, t1.lib_numMep, t1.lib_id, t1.lib_user, t1.lib_serie,
		t1.lib_id_box, t1.lib_description, t1.lib_nick, t1.lib_pass, t1.lib_imei, t1.lib_rm, t1.libimei_code, t1.libimei_number, t1.libimei_date, t1.libimei_date_liberation, 
		t1.libimei_number_liberation, t1.libimei_status, t1.libimei_bonus, t1.libimei_original, t1.libimei_renombre, t1.libimei_verification, t1.libimei_text_cable, 
		t1.API_provider_code, t1.ser_name, t1.img_rename, t1.API_order_id, t1.cli_name, t1.cli_last_name, t1.cli_email, t1.cli_payment_form, t1.cli_rename, t1.cli_status, 
		st.type_service
		FROM
		
		(SELECT t.lib_code, t.ser_code, t.cli_code, t.lib_date, t.lib_country, t.lib_model, t.lib_operator, t.lib_numMep, t.lib_id, t.lib_user, t.lib_serie, t.lib_id_box,
		t.lib_description, t.lib_nick, t.lib_pass, t.lib_imei, t.lib_rm, t.libimei_code, t.libimei_number, t.libimei_date, t.libimei_date_liberation, 
		t.libimei_number_liberation, t.libimei_status, t.libimei_bonus, t.libimei_original, t.libimei_renombre, t.libimei_verification, t.libimei_text_cable, 
		t.API_provider_code, t.ser_name, t.img_rename, t.ser_type_code, t.API_order_id, c.cli_name, c.cli_last_name, c.cli_email, c.cli_payment_form, c.cli_rename, c.cli_status
		FROM
				
		(SELECT t0.lib_code, t0.ser_code, t0.cli_code, t0.lib_date, t0.lib_country, t0.lib_model, t0.lib_operator, t0.lib_numMep, t0.lib_id, t0.lib_user, t0.lib_serie,
		t0.lib_id_box, t0.lib_description, t0.lib_nick, t0.lib_pass, t0.lib_imei, t0.lib_rm, t0.libimei_code, t0.libimei_number, t0.libimei_date, t0.libimei_date_liberation, 
		t0.libimei_number_liberation, t0.libimei_status, t0.libimei_bonus, t0.libimei_original, t0.libimei_renombre, t0.libimei_verification, t0.libimei_text_cable, 
		t0.API_provider_code, t0.API_order_id, s.ser_name, s.img_rename, s.ser_type_code
		FROM
		
		(SELECT l.lib_code, l.ser_code, l.cli_code, l.lib_date, l.lib_country, l.lib_model, l.lib_operator, l.lib_numMep, l.lib_id, l.lib_user, l.lib_serie, l.lib_id_box,
		l.lib_description, l.lib_nick, l.lib_pass, l.lib_imei, l.lib_rm , li.libimei_code, li.libimei_number, li.libimei_date, li.libimei_date_liberation,
		libimei_number_liberation, li.libimei_status, li.libimei_bonus, li.libimei_original, li.libimei_renombre, li.libimei_verification, li.libimei_text_cable, 
		li.API_provider_code, li.API_order_id
		FROM liberations l JOIN operator_notice li 
		ON l.lib_code=li.lib_code ".$whereCliCode.") t0
		JOIN services s ON s.ser_code = t0.ser_code) t
		JOIN client c ON t.cli_code = c.cli_code) t1
		JOIN service_type st ON t1.ser_type_code = st.ser_type_code) t2
		LEFT JOIN model m ON t2.lib_model = m.mod_code) t3
		LEFT JOIN brand b ON t3.bra_code = b.bra_code) t4
		LEFT JOIN red r ON t4.lib_operator = r.red_id) t5
		LEFT JOIN country c ON t5.lib_country = c.cou_code) t6
		LEFT JOIN api ON t6.API_provider_code = api.api_code ".$where.$order_by;*/
		
		$sql = "
		SELECT t2.lib_code, t2.ser_code, t2.cli_code, t2.lib_imei, t2.libimei_code, t2.libimei_number, t2.libimei_date, t2.libimei_date_liberation, 
		t2.libimei_number_liberation, t2.libimei_status, t2.libimei_bonus, t2.libimei_original, t2.libimei_renombre, t2.libimei_verification, 
		t2.API_provider_code, t2.ser_name, t2.img_rename, t2.API_order_id, t2.cli_name, t2.cli_last_name, t2.cli_email, t2.cli_payment_form,
		t2.type_service, api.api_name
		FROM		
		(SELECT t1.lib_code, t1.ser_code, t1.cli_code, t1.lib_imei,  t1.libimei_code, t1.libimei_number, t1.libimei_date, t1.libimei_date_liberation, 
		t1.libimei_number_liberation, t1.libimei_status, t1.libimei_bonus, t1.libimei_original, t1.libimei_renombre, t1.libimei_verification, 
		t1.API_provider_code, t1.ser_name, t1.img_rename, t1.API_order_id, t1.cli_name, t1.cli_last_name, t1.cli_email, t1.cli_payment_form,
		st.type_service
		FROM
		
		(SELECT t.lib_code, t.ser_code, t.cli_code, t.lib_imei, t.libimei_code, t.libimei_number, t.libimei_date, t.libimei_date_liberation, 
		t.libimei_number_liberation, t.libimei_status, t.libimei_bonus, t.libimei_original, t.libimei_renombre, t.libimei_verification, 
		t.API_provider_code, t.ser_name, t.img_rename, t.ser_type_code, t.API_order_id, c.cli_name, c.cli_last_name, c.cli_email, c.cli_payment_form
		FROM
				
		(SELECT t0.lib_code, t0.ser_code, t0.cli_code, t0.lib_imei, t0.libimei_code, t0.libimei_number, t0.libimei_date, t0.libimei_date_liberation, 
		t0.libimei_number_liberation, t0.libimei_status, t0.libimei_bonus, t0.libimei_original, t0.libimei_renombre, t0.libimei_verification, 
		t0.API_provider_code, t0.API_order_id, s.ser_name, s.img_rename, s.ser_type_code
		FROM
		
		(SELECT l.lib_code, l.ser_code, l.cli_code, l.lib_imei, li.libimei_code, li.libimei_number, li.libimei_date, li.libimei_date_liberation,
		li.libimei_number_liberation, li.libimei_status, li.libimei_bonus, li.libimei_original, li.libimei_renombre, li.libimei_verification, 
		li.API_provider_code, li.API_order_id
		FROM liberations l JOIN operator_notice li 
		ON l.lib_code=li.lib_code ".$whereCliCode.") t0
		JOIN services s ON s.ser_code = t0.ser_code) t
		JOIN client c ON t.cli_code = c.cli_code) t1
		JOIN service_type st ON t1.ser_type_code = st.ser_type_code) t2
		LEFT JOIN api ON t2.API_provider_code = api.api_code ".$where.$order_by;
		
		/*echo $sql = "
		SELECT libe.lib_code, libe.ser_code, libe.cli_code, libe.lib_imei,  liim.libimei_code, liim.libimei_number, liim.libimei_date, liim.libimei_date_liberation, 
		liim.libimei_number_liberation, liim.libimei_status, liim.libimei_bonus, liim.libimei_original, liim.libimei_renombre, liim.libimei_verification, 
		liim.API_provider_code, ser.ser_name, ser.img_rename, API_order_id, cli.cli_name, cli.cli_last_name, cli.cli_email, cli.cli_payment_form,
		type_service, api_name 
		FROM 
		operator_notice AS liim, liberations AS libe, services AS ser, client AS cli, service_type AS sety, api
		WHERE
		liim.lib_code = libe.lib_code AND libe.ser_code = ser.ser_code AND cli.cli_code = libe.cli_code AND ser.ser_type_code = sety.ser_type_code AND  liim.API_provider_code = api.api_code ".$whereCliCode.$where.$order_by;*/
		
		if(!($inicio == 0 && $cantidad == 0))
			$sql .= ' LIMIT '.(int) $inicio.', '.(int) $cantidad.';';
		//echo $sql;
		if($consult  = mysql_query($sql)){
			$data =array();
			$i = 0;
			$new = 0;//cuando no tenemos liberaciones nuevas en ese rango de horas.
			
			while($row = mysql_fetch_array($consult)){
				$data[$i]->ser_code = $row['ser_code'];
				$data[$i]->cli_code = $row['cli_code'];
				$data[$i]->cli_name = $row['cli_name'];
				$data[$i]->cli_last_name = $row['cli_last_name'];
				$data[$i]->cli_email = $row['cli_email'];
				$data[$i]->cli_payment_form = $row['cli_payment_form'];
				$data[$i]->ser_name = $row['ser_name'];
				$data[$i]->ser_img_rename= $row['img_rename'];
				$data[$i]->lib_imei = $row['lib_imei'];
				$data[$i]->libImei_code = $row['libimei_code'];
				$data[$i]->libImei_number = $row['libimei_number'];
				$data[$i]->libImei_date = $row['libimei_date'];
				$data[$i]->libImei_date_lib = $row['libimei_date_liberation'];
				$data[$i]->libImei_num_lib = $row['libimei_number_liberation'];
				$data[$i]->libImei_status = $row['libimei_status'];
				$data[$i]->libimei_bonus = $row['libimei_bonus'];
				$data[$i]->type_service = $row['type_service'];
				$data[$i]->libimei_original = $row['libimei_original'];
				$data[$i]->libimei_renombre = $row['libimei_renombre'];
				$data[$i]->libimei_verification = $row['libimei_verification'];
				
				//--------------------------------------------------------------
				
				$data[$i]->API_provider_code = $row["API_provider_code"];
				$data[$i]->API_order_id = $row["API_order_id"];
				$data[$i]->api_name = $row["api_name"];
				
				$i++;				
			}
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/**
	*	Función encargada de obtener la cantida de liberaciones nuevas.
	*	@autor: JSL
	*	@date: 1 DIC 2011
	*	@parameters:	
	*		$minute: sirve para saber a partir de cuantos minutos antes contar las nuevas liberaciones, por defecto se revisa un minuto antes de la hora.
	*/
	function countNewsUnlock($minute = 1){
		$dateAlert = date('Y-m-d H:i:s',mktime(date('H'),date('i')-$minute,date('s'),date('m'),date('d'),date('Y')));
		$sql = "SELECT COUNT(*) AS cant FROM operator_notice WHERE libimei_date > '".$dateAlert."' AND libimei_status = '0';";
		if($result = mysql_query($sql)){
			$row = mysql_fetch_array($result);
			return $row["cant"];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/**
	*	Función encargada de obtener la cantida de peticiones de credito pendiente por aprobar.
	*	@autor: JSL
	*	@date: 1 DIC 2011
	*/
	function countPendingCredit(){
		$sql = "
			SELECT sum(pending_credit) as amount_pending
				FROM  
					(SELECT cli_code FROM client WHERE `cli_status` <>4) cli
				LEFT JOIN 
					(		
					SELECT count( bocl_code ) AS pending_credit, cli_code as id
					FROM bonus_client
					WHERE bocl_status = 'Inactivo' GROUP BY cli_code
					) credit_client 
			ON cli.cli_code = credit_client.id ";
		if($result = mysql_query($sql)){
			$row = mysql_fetch_array($result);
			return $row["amount_pending"];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////
	// countLiberationsImeisByClient: Mostrar las liberaciones de cada imei por cliente, es decir por codigo de cliente, aqa listamos la cantidad.
	//
	// parameter: seach --> sirve para buscar por nombre, telefono, email o login (puede venir vacio)
	//
	// Retorna: la cantidad de clientes encontrados
	//
	// Autor: j.s.l.p
	// Fecha de Creación: 05/06/2010
	//
	// Modificaciones:
	///////////////////////////////////////////////////////////////////////////////
	function countLiberationsImeisByClient($cliCode = '',$search='',$status = '',$group='',$service='',$imei='',$createFrom='', $createUntil='',$lib='',$code_imei=''){
		
		include_once("RijndaelHex.php");
	
		$rijndael = new RijndaelHex();
		
		$where = '';
		if($search != '')
			$where = " WHERE (cli_name like '%".$search."%' 
						OR cli_last_name like '%".$search."%'
						OR cli_email like '%".$search."%'
						OR libimei_date like '%".$search."%'
						OR libimei_number like '%".$search."%'
						OR libimei_number_liberation like '%".$search."%'
						OR libimei_date_liberation like '%".$search."%'
						OR libimei_status like '%".$search."%')";
		
		$whereCliCode = '';
		if((int) $cliCode > 0)
			$whereCliCode = " WHERE cli_code = ".(int) $cliCode;
		
		if($status != '' && $status != '-3'){
			//0:no liberado , 1:liberado , 2:se trato de liberar pero no se pudo , 3: Not In Data Base.			
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			if($status == -1)//  para la lista de imeis pendientes: No liberados y en verificacion
				$where .= " (libimei_status = 0 OR libimei_status = 2)";	
			elseif($status == -2)// para la lista de imeis liberados y que no existen.
				$where .= " (libimei_status = 1 OR libimei_status = 3)";				
			else
				$where .= " libimei_status = ".(int)$status;
		}			
		elseif($status == -3){// para la todos los estados
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where = " (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3)";
		}
		
		if($group != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " type_service='".$group."' ";
		}
		
		if($service != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " ser_code='".(int)$service."' ";		
		}
		
		if($imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_number like '%".$rijndael->linencrypthex($imei)."%' OR lib_imei like '%".$rijndael->linencrypthex($imei)."%' OR libimei_number like '%".$imei."%' OR lib_imei like '%".$imei."%'";	
		}
		
		if($createFrom != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " libimei_date >= '".$createFrom."' ";
		}
		
		if($createUntil != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_date <= '".$createUntil."' ";	
		}
		
		if($lib != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " lib_code = '".(int)$lib."' ";
		}
		
		if($code_imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libImei_code = '".(int)$code_imei."' ";		
		}
		//echo '<pre>'.$sql.'</pre>';
		
		$sql = "
		SELECT COUNT(*) AS cant
		FROM
		
		(SELECT t.lib_code, t.ser_code, t.cli_code, t.lib_imei, t.libimei_code, t.libimei_number, t.libimei_date, t.libimei_date_liberation, 
		t.libimei_number_liberation, t.libimei_status, t.libimei_bonus, t.libimei_original, t.libimei_renombre, t.libimei_verification, 
		t.API_provider_code, t.ser_name, t.img_rename, t.ser_type_code, t.API_order_id, c.cli_name, c.cli_last_name, c.cli_email, c.cli_payment_form
		FROM
				
		(SELECT t0.lib_code, t0.ser_code, t0.cli_code, t0.lib_imei, t0.libimei_code, t0.libimei_number, t0.libimei_date, t0.libimei_date_liberation, 
		t0.libimei_number_liberation, t0.libimei_status, t0.libimei_bonus, t0.libimei_original, t0.libimei_renombre, t0.libimei_verification, 
		t0.API_provider_code, t0.API_order_id, s.ser_name, s.img_rename, s.ser_type_code
		FROM
		
		(SELECT l.lib_code, l.ser_code, l.cli_code, l.lib_imei, li.libimei_code, li.libimei_number, li.libimei_date, li.libimei_date_liberation,
		li.libimei_number_liberation, li.libimei_status, li.libimei_bonus, li.libimei_original, li.libimei_renombre, li.libimei_verification, 
		li.API_provider_code, li.API_order_id
		FROM liberations l JOIN operator_notice li 
		ON l.lib_code=li.lib_code ".$whereCliCode.") t0
		JOIN services s ON s.ser_code = t0.ser_code) t
		JOIN client c ON t.cli_code = c.cli_code) t1
		JOIN service_type st ON t1.ser_type_code = st.ser_type_code ".$where;
		
		if( $result = mysql_query($sql) ){
			$row = mysql_fetch_array($result);
			return $row["cant"];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	function countLiberationsImeisByClientOptimized($cliCode = '',$search='',$status = '',$group='',$service='',$imei='',$createFrom='', $createUntil='',$lib='',$code_imei=''){
		
		include_once("RijndaelHex.php");
	
		$rijndael = new RijndaelHex();
		
		$where = '';
		if($search != '')
			$where = " WHERE (cli_name like '%".$search."%' 
						OR cli_last_name like '%".$search."%'
						OR cli_email like '%".$search."%'
						OR libimei_date like '%".$search."%'
						OR libimei_number like '%".$search."%'
						OR libimei_number_liberation like '%".$search."%'
						OR libimei_date_liberation like '%".$search."%'
						OR libimei_status like '%".$search."%')";
		
		$whereCliCode = '';
		if((int) $cliCode > 0)
			$whereCliCode = " WHERE l.cli_code = ".(int) $cliCode;
		
		if($status != '' && $status != '-3'){
			//0:no liberado , 1:liberado , 2:se trato de liberar pero no se pudo , 3: Not In Data Base.			
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			if($status == -1)//  para la lista de imeis pendientes: No liberados y en verificacion
				$where .= " (libimei_status = 0 OR libimei_status = 2)";	
			elseif($status == -2)// para la lista de imeis liberados y que no existen.
				$where .= " (libimei_status = 1 OR libimei_status = 3)";				
			else
				$where .= " libimei_status = ".(int)$status;
		}			
		elseif($status == -3){// para la todos los estados
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where = " (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3)";
		}
		
		if($group != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " type_service='".$group."' ";
		}
		
		if($service != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " ser_code='".(int)$service."' ";		
		}
		
		if($imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_number like '%".$rijndael->linencrypthex($imei)."%' OR lib_imei like '%".$rijndael->linencrypthex($imei)."%' OR libimei_number like '%".$imei."%' OR lib_imei like '%".$imei."%'";	
		}
		
		if($createFrom != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " libimei_date >= '".$createFrom."' ";
		}
		
		if($createUntil != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_date <= '".$createUntil."' ";	
		}
		
		if($lib != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " lib_code = '".(int)$lib."' ";
		}
		
		if($code_imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libImei_code = '".(int)$code_imei."' ";		
		}
		//echo '<pre>'.$sql.'</pre>';
		
		$sql = "
		SELECT COUNT(*) AS cant
		FROm (select l.lib_imei, cli_name, s.ser_code, c.cli_code, cli_last_name, cli_email, cli_payment_form,ser_name, s.img_rename, op.libimei_code, libimei_number, libimei_date, libimei_date_liberation, libimei_number_liberation, libimei_status, libimei_bonus, ts.type_service, libimei_original, libimei_renombre, libimei_verification, op.API_provider_code,op.API_order_id FROM operator_notice op, liberations l, client c, services s, service_type ts WHERE op.lib_code=l.lib_code AND c.cli_code = l.cli_code AND s.ser_code = l.ser_code AND ts.ser_type_code = s.ser_type_code AND (libimei_status = 0 OR libimei_status = 2) ".$whereCliCode.")t2 LEFT JOIN api ON t2.API_provider_code = api.api_code ".$where;
		
		if( $result = mysql_query($sql) ){
			$row = mysql_fetch_array($result);
			return $row["cant"];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	function countLiberationsImeisByClientOptimized2($cliCode = '',$search='',$status = '',$group='',$service='',$imei='',$createFrom='', $createUntil='',$lib='',$code_imei=''){
		
		include_once("RijndaelHex.php");
	
		$rijndael = new RijndaelHex();
		
		$where = '';
		if($search != '')
			$where = " WHERE (cli_name like '%".$search."%' 
						OR cli_last_name like '%".$search."%'
						OR cli_email like '%".$search."%'
						OR libimei_date like '%".$search."%'
						OR libimei_number like '%".$search."%'
						OR libimei_number_liberation like '%".$search."%'
						OR libimei_date_liberation like '%".$search."%'
						OR libimei_status like '%".$search."%')";
		
		$whereCliCode = '';
		if((int) $cliCode > 0)
			$whereCliCode = " WHERE l.cli_code = ".(int) $cliCode;
		
		if($status != '' && $status != '-3'){
			//0:no liberado , 1:liberado , 2:se trato de liberar pero no se pudo , 3: Not In Data Base.			
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			if($status == -1)//  para la lista de imeis pendientes: No liberados y en verificacion
				$where .= " (libimei_status = 0 OR libimei_status = 2)";	
			elseif($status == -2)// para la lista de imeis liberados y que no existen.
				$where .= " (libimei_status = 1 OR libimei_status = 3)";				
			else
				$where .= " libimei_status = ".(int)$status;
		}			
		elseif($status == -3){// para la todos los estados
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where = " (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3)";
		}
		
		if($group != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " type_service='".$group."' ";
		}
		
		if($service != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " ser_code='".(int)$service."' ";		
		}
		
		if($imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_number like '%".$rijndael->linencrypthex($imei)."%' OR lib_imei like '%".$rijndael->linencrypthex($imei)."%' OR libimei_number like '%".$imei."%' OR lib_imei like '%".$imei."%'";	
		}
		
		if($createFrom != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " libimei_date >= '".$createFrom."' ";
		}
		
		if($createUntil != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_date <= '".$createUntil."' ";	
		}
		
		if($lib != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " lib_code = '".(int)$lib."' ";
		}
		
		if($code_imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libImei_code = '".(int)$code_imei."' ";		
		}
		//echo '<pre>'.$sql.'</pre>';
		
		$sql = "
		SELECT COUNT(*) AS cant
		FROm (select l.lib_imei, cli_name, s.ser_code, c.cli_code, cli_last_name, cli_email, cli_payment_form,ser_name, s.img_rename, op.libimei_code, libimei_number, libimei_date, libimei_date_liberation, libimei_number_liberation, libimei_status, libimei_bonus, ts.type_service, libimei_original, libimei_renombre, libimei_verification, op.API_provider_code,op.API_order_id FROM operator_notice op, liberations l, client c, services s, service_type ts WHERE op.lib_code=l.lib_code AND c.cli_code = l.cli_code AND s.ser_code = l.ser_code AND ts.ser_type_code = s.ser_type_code AND (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3) ".$whereCliCode.")t2 LEFT JOIN api ON t2.API_provider_code = api.api_code ".$where;
		
		if( $result = mysql_query($sql) ){
			$row = mysql_fetch_array($result);
			return $row["cant"];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	function countLiberationsImeisByClientPublic($cliCode = '',$search='',$status = '',$group='',$service='',$imei='',$createFrom='', $createUntil='',$lib='',$code_imei=''){
		
		//include_once("RijndaelHex.php");
	
		$rijndael = new RijndaelHex();
		
		$where = '';
		if($search != '')
			$where = " WHERE (cli_name like '%".$search."%' 
						OR cli_last_name like '%".$search."%'
						OR cli_email like '%".$search."%'
						OR libimei_date like '%".$search."%'
						OR libimei_number like '%".$search."%'
						OR libimei_number_liberation like '%".$search."%'
						OR libimei_date_liberation like '%".$search."%'
						OR libimei_status like '%".$search."%')";
		
		$whereCliCode = '';
		if((int) $cliCode > 0)
			$whereCliCode = " WHERE cli_code = ".(int) $cliCode;
		
		if($status != '' && $status != '-3'){
			//0:no liberado , 1:liberado , 2:se trato de liberar pero no se pudo , 3: Not In Data Base.			
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			if($status == -1)//  para la lista de imeis pendientes: No liberados y en verificacion
				$where .= " (libimei_status = 0 OR libimei_status = 2)";	
			elseif($status == -2)// para la lista de imeis liberados y que no existen.
				$where .= " (libimei_status = 1 OR libimei_status = 3)";				
			else
				$where .= " libimei_status = ".(int)$status;
		}			
		elseif($status == -3){// para la todos los estados
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where = " (libimei_status = 0 OR libimei_status = 1 OR libimei_status = 2 OR libimei_status = 3)";
		}
		
		if($group != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " type_service='".$group."' ";
		}
		
		if($service != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " ser_code='".(int)$service."' ";		
		}
		
		if($imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_number like '%".$imei."%' OR lib_imei like '%".$imei."%' ";	
		}
		
		if($createFrom != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			
			$where .= " libimei_date >= '".$createFrom."' ";
		}
		
		if($createUntil != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libimei_date <= '".$createUntil."' ";	
		}
		
		if($lib != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " lib_code = '".(int)$lib."' ";
		}
		
		if($code_imei != ''){
			if($where != '')
				$where .= ' AND ';
			else
				$where .= ' WHERE ';
			$where .= " libImei_code = '".(int)$code_imei."' ";		
		}
		//echo '<pre>'.$sql.'</pre>';
		
		$sql = "
		SELECT COUNT(*) AS cant
		FROM
		
		(SELECT t.lib_code, t.ser_code, t.cli_code, t.lib_imei, t.libimei_code, t.libimei_number, t.libimei_date, t.libimei_date_liberation, 
		t.libimei_number_liberation, t.libimei_status, t.libimei_bonus, t.libimei_original, t.libimei_renombre, t.libimei_verification, 
		t.API_provider_code, t.ser_name, t.img_rename, t.ser_type_code, t.API_order_id, c.cli_name, c.cli_last_name, c.cli_email, c.cli_payment_form
		FROM
				
		(SELECT t0.lib_code, t0.ser_code, t0.cli_code, t0.lib_imei, t0.libimei_code, t0.libimei_number, t0.libimei_date, t0.libimei_date_liberation, 
		t0.libimei_number_liberation, t0.libimei_status, t0.libimei_bonus, t0.libimei_original, t0.libimei_renombre, t0.libimei_verification, 
		t0.API_provider_code, t0.API_order_id, s.ser_name, s.img_rename, s.ser_type_code
		FROM
		
		(SELECT l.lib_code, l.ser_code, l.cli_code, l.lib_imei, li.libimei_code, li.libimei_number, li.libimei_date, li.libimei_date_liberation,
		li.libimei_number_liberation, li.libimei_status, li.libimei_bonus, li.libimei_original, li.libimei_renombre, li.libimei_verification, 
		li.API_provider_code, li.API_order_id
		FROM liberations l JOIN operator_notice li 
		ON l.lib_code=li.lib_code ".$whereCliCode.") t0
		JOIN services s ON s.ser_code = t0.ser_code) t
		JOIN client c ON t.cli_code = c.cli_code) t1
		JOIN service_type st ON t1.ser_type_code = st.ser_type_code ".$where;
		
		if( $result = mysql_query($sql) ){
			$row = mysql_fetch_array($result);
			return $row["cant"];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	// esta funcion inserta el codigo de liberacion de un imei.
	function insertCodeLiberation($data)
	{
		$sqlAntes="
		UPDATE `operator_notice` SET 
		`libimei_number_liberation` = '".$data->number_lib."',
		`libimei_date_liberation` = '".date("Y-m-d H:i:s")."',
		`libimei_text_cable` = '".$data->comment."',
		`libimei_status` = '1' 
		WHERE `libimei_code` =".$data->code_imei.";";
		//die($sqlAntes);
		mysql_query($sqlAntes);
		
		
		$sql="
		UPDATE `liberations_imei` SET 
		`libimei_number_liberation` = '".$data->number_lib."',
		`libimei_date_liberation` = '".date("Y-m-d H:i:s")."',
		`libimei_text_cable` = '".$data->comment."',
		`libimei_status` = '1' 
		WHERE `libimei_code` =".$data->code_imei.";";
		
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	// cambia el estado a una cuando la liberacion es realizada por cable.
	function changeCableLiberationStatus($data_libe)
	{
		$sql="
		UPDATE `operator_notice` SET 
		`libimei_date_liberation` = '".date("Y-m-d H:i:s")."',
		`libimei_text_cable` = '".$data_libe->comment."',
		`libimei_status` = '1' 
		WHERE `libimei_code` =".$data_libe->code_imei;		
		
		if(mysql_query($sql))
		    return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}	
	}
	
	// funcion encargada de cambiar el estado de un imei
	function changeStatusImei($code_imei,$status){
		$sql = "UPDATE `operator_notice` SET `libimei_status` = '".$status."', libimei_date_liberation = '".date("Y-m-d H:i:s")."' WHERE `libimei_code` =".$code_imei;
		if(mysql_query($sql))
		    return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////
	// Esta funcion es la encargada de obtener los datos de una liberacion , imei cable o archivo
	/////////////////////////////////////////////////////////////////////////
	function getDataImei($libimei_code){		
		$sql = "
		SELECT t5.lib_code, t5.ser_code, t5.cli_code, t5.lib_date, t5.lib_country, t5.lib_model, t5.lib_operator, t5.lib_numMep, t5.lib_id, t5.lib_user, t5.lib_serie,
		t5.lib_id_box, t5.lib_description, t5.lib_nick, t5.lib_pass, t5.lib_imei, t5.lib_rm , t5.libimei_code, t5.libimei_number, t5.libimei_date, t5.libimei_date_liberation, 
		t5.libimei_number_liberation, t5.libimei_status, t5.libimei_bonus, t5.libimei_original, t5.libimei_renombre, t5.libimei_verification, t5.libimei_text_cable, 
		t5.API_provider_code, t5.ser_name, t5.img_rename, t5.API_order_id, t5.cli_name, t5.cli_last_name, t5.cli_email, t5.cli_payment_form, t5.cli_rename, t5.cli_status, 
		t5.type_service, t5.mod_name, t5.bra_name, t5.red_name, c.cou_name
		FROM
		
		(SELECT t4.lib_code, t4.ser_code, t4.cli_code, t4.lib_date, t4.lib_country, t4.lib_model, t4.lib_operator, t4.lib_numMep, t4.lib_id, t4.lib_user, t4.lib_serie,
		t4.lib_id_box, t4.lib_description, t4.lib_nick, t4.lib_pass, t4.lib_imei, t4.lib_rm, t4.libimei_code, t4.libimei_number, t4.libimei_date, t4.libimei_date_liberation, 
		t4.libimei_number_liberation, t4.libimei_status, t4.libimei_bonus, t4.libimei_original, t4.libimei_renombre, t4.libimei_verification, t4.libimei_text_cable, 
		t4.API_provider_code, t4.ser_name, t4.img_rename, t4.API_order_id, t4.cli_name, t4.cli_last_name, t4.cli_email, t4.cli_payment_form, t4.cli_rename, t4.cli_status, 
		t4.type_service, t4.mod_name, t4.bra_name, r.red_name
		FROM
		
		(SELECT t3.lib_code, t3.ser_code, t3.cli_code, t3.lib_date, t3.lib_country, t3.lib_model, t3.lib_operator, t3.lib_numMep, t3.lib_id, t3.lib_user, t3.lib_serie,
		t3.lib_id_box, t3.lib_description, t3.lib_nick, t3.lib_pass, t3.lib_imei, t3.lib_rm, t3.libimei_code, t3.libimei_number, t3.libimei_date, t3.libimei_date_liberation,
		t3.libimei_number_liberation, t3.libimei_status, t3.libimei_bonus, t3.libimei_original, t3.libimei_renombre, t3.libimei_verification, t3.libimei_text_cable, 
		t3.API_provider_code, t3.ser_name, t3.img_rename, t3.API_order_id, t3.cli_name, t3.cli_last_name, t3.cli_email, t3.cli_payment_form, t3.cli_rename, t3.cli_status, 
		t3.type_service, t3.mod_name, b.bra_name
		FROM
		(SELECT t2.lib_code, t2.ser_code, t2.cli_code, t2.lib_date, t2.lib_country, t2.lib_model, t2.lib_operator, t2.lib_numMep, t2.lib_id, t2.lib_user, t2.lib_serie,
		t2.lib_id_box, t2.lib_description, t2.lib_nick, t2.lib_pass, t2.lib_imei, t2.lib_rm, t2.libimei_code, t2.libimei_number, t2.libimei_date, t2.libimei_date_liberation, 
		t2.libimei_number_liberation, t2.libimei_status, t2.libimei_bonus, t2.libimei_original, t2.libimei_renombre, t2.libimei_verification, t2.libimei_text_cable, 
		t2.API_provider_code, t2.ser_name, t2.img_rename, t2.API_order_id, t2.cli_name, t2.cli_last_name, t2.cli_email, t2.cli_payment_form, t2.cli_rename, t2.cli_status, 
		t2.type_service, m.mod_name, m.bra_code
		FROM
		
		(SELECT t1.lib_code, t1.ser_code, t1.cli_code, t1.lib_date, t1.lib_country, t1.lib_model, t1.lib_operator, t1.lib_numMep, t1.lib_id, t1.lib_user, t1.lib_serie,
		t1.lib_id_box, t1.lib_description, t1.lib_nick, t1.lib_pass, t1.lib_imei, t1.lib_rm, t1.libimei_code, t1.libimei_number, t1.libimei_date, t1.libimei_date_liberation, 
		t1.libimei_number_liberation, t1.libimei_status, t1.libimei_bonus, t1.libimei_original, t1.libimei_renombre, t1.libimei_verification, t1.libimei_text_cable, 
		t1.API_provider_code, t1.ser_name, t1.img_rename, t1.API_order_id, t1.cli_name, t1.cli_last_name, t1.cli_email, t1.cli_payment_form, t1.cli_rename, t1.cli_status, 
		st.type_service
		FROM
		
		(SELECT t.lib_code, t.ser_code, t.cli_code, t.lib_date, t.lib_country, t.lib_model, t.lib_operator, t.lib_numMep, t.lib_id, t.lib_user, t.lib_serie, t.lib_id_box,
		t.lib_description, t.lib_nick, t.lib_pass, t.lib_imei, t.lib_rm, t.libimei_code, t.libimei_number, t.libimei_date, t.libimei_date_liberation, 
		t.libimei_number_liberation, t.libimei_status, t.libimei_bonus, t.libimei_original, t.libimei_renombre, t.libimei_verification, t.libimei_text_cable, 
		t.API_provider_code, t.ser_name, t.img_rename, t.ser_type_code, t.API_order_id, c.cli_name, c.cli_last_name, c.cli_email, c.cli_payment_form, c.cli_rename, c.cli_status
		FROM
				
		(SELECT t0.lib_code, t0.ser_code, t0.cli_code, t0.lib_date, t0.lib_country, t0.lib_model, t0.lib_operator, t0.lib_numMep, t0.lib_id, t0.lib_user, t0.lib_serie,
		t0.lib_id_box, t0.lib_description, t0.lib_nick, t0.lib_pass, t0.lib_imei, t0.lib_rm, t0.libimei_code, t0.libimei_number, t0.libimei_date, t0.libimei_date_liberation, 
		t0.libimei_number_liberation, t0.libimei_status, t0.libimei_bonus, t0.libimei_original, t0.libimei_renombre, t0.libimei_verification, t0.libimei_text_cable, 
		t0.API_provider_code, t0.API_order_id, s.ser_name, s.img_rename, s.ser_type_code
		FROM
		
		(SELECT l.lib_code, l.ser_code, l.cli_code, l.lib_date, l.lib_country, l.lib_model, l.lib_operator, l.lib_numMep, l.lib_id, l.lib_user, l.lib_serie, l.lib_id_box,
		l.lib_description, l.lib_nick, l.lib_pass, l.lib_imei, l.lib_rm , li.libimei_code, li.libimei_number, li.libimei_date, li.libimei_date_liberation,
		libimei_number_liberation, li.libimei_status, li.libimei_bonus, li.libimei_original, li.libimei_renombre, li.libimei_verification, li.libimei_text_cable, 
		li.API_provider_code, li.API_order_id
		FROM liberations l JOIN operator_notice li 
		ON l.lib_code=li.lib_code WHERE li.libimei_code = ".$libimei_code.") t0
		JOIN services s ON s.ser_code = t0.ser_code) t
		JOIN client c ON t.cli_code = c.cli_code) t1
		JOIN service_type st ON t1.ser_type_code = st.ser_type_code) t2
		LEFT JOIN model m ON t2.lib_model = m.mod_code) t3
		LEFT JOIN brand b ON t3.bra_code = b.bra_code) t4
		LEFT JOIN red r ON t4.lib_operator = r.red_id) t5
		LEFT JOIN country c ON t5.lib_country = c.cou_code
		";
		
		if($consult  = mysql_query($sql)){
			
			$row = mysql_fetch_array($consult);
			
			$data->code = $row['libimei_code'];
			$data->imei_status = $row['libimei_status'];//0:no liberado 1:liberado 2:se trato de liberar pero no se pudo.3: Not In Data Base.		
			$data->libimei_number = $row['libimei_number'];	
			$data->libimei_date = $row['libimei_date'];
			$data->libimei_date_liberation = $row['libimei_date_liberation'];//Fecha de liberacion de cada imei o fecha en que fue modificado
			$data->libimei_number_liberation = $row['libimei_number_liberation'];//Numero de liberacion por cada imei	
			$data->libimei_bonus = $row['libimei_bonus'];//Creditos que consumio la liberacion.
			$data->libimei_money = $row['libimei_money'];//COL: moneda colombiana USD: Dolar BOL: Moneda de venezuela
			
			$data->cli_code = $row['cli_code'];
			$data->cli_name = $row['cli_name'];
			$data->cli_last_name = $row['cli_last_name'];
			$data->cli_email = $row['cli_email'];
						
			$data->cli_payment_form = $row['cli_payment_form'];
			$data->cli_rename = $row['cli_rename'];
			$data->cli_status = $row['cli_status'];
			
			$data->ser_name = $row['ser_name'];					
			$data->ser_img_rename = $row['img_rename'];	
							
			$data->type_service = $row['type_service'];				
			$data->lib_description = $row['lib_description'];
								 
			//archivo
			$data->libimei_original = $row['libimei_original'];
			$data->libimei_renombre = $row['libimei_renombre'];
			
			$data->lib_model = $row['lib_model'];
		
			$data->lib_model_name	= $row['mod_name'];
			$data->lib_brand_name	= $row['bra_name'];			
			
			$data->lib_operator = $row['lib_operator'];
			$data->lib_operator_name = $row['red_name'];
			
			$data->lib_country = $row['lib_country'];
			$data->lib_country_name = $row["cou_name"];
			
			$data->lib_numMep = $row['lib_numMep'];				
			$data->lib_id = $row['lib_id'];
			$data->lib_serie = $row['lib_serie'];
			$data->lib_user = $row['lib_user'];
			$data->lib_id_box = $row['lib_id_box'];
			
			$data->lib_nick = $row['lib_nick'];
			$data->lib_pass = $row['lib_pass'];
			$data->lib_imei = $row['lib_imei'];
			$data->lib_rm = $row['lib_rm'];
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargadda de dar el nombre de un operador.
	/////////////////////////////////////////////////////////////////////////////
	function getNameOperator($red_id)
	{
		$sql = "SELECT red_name FROM red WHERE red_id=".$red_id;
		if($consult  = mysql_query($sql)){		
			$row = mysql_fetch_array($consult);
			return $row['red_name'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargadda de dar el nombre de un modelo.
	/////////////////////////////////////////////////////////////////////////////
	function getNameModel($mod_code)
	{
		$sql = "SELECT mod_name FROM model WHERE mod_code=".$mod_code;
		if($consult  = mysql_query($sql)){		
			$row = mysql_fetch_array($consult);
			return $row['mod_name'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargadda de dar nombre de una marca, recibiendo el cod de un modelo como param.
	/////////////////////////////////////////////////////////////////////////////
	function getBrandName($mod_code)
	{
		$sql = "
		SELECT bra_name FROM brand brn, model mdel
		WHERE mdel.bra_code = brn.bra_code AND mdel.mod_code=".$mod_code;
		if($consult  = mysql_query($sql)){		
			$row = mysql_fetch_array($consult);
			return $row['bra_name'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////
	// funcion encargadda de dar el nombre de un modelo.
	/////////////////////////////////////////////////////////////////////////////
	function getNameCountry($cou_code)
	{
		$sql = "SELECT cou_name FROM country WHERE cou_code=".$cou_code;
		if($consult  = mysql_query($sql)){		
			$row = mysql_fetch_array($consult);
			return $row['cou_name'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	//funcion encargada de cambiar el password de un cliente.
	function changePasswordClient($data){
		$sql = "UPDATE `client` SET `cli_password` = '".$data->new_pass."' WHERE `cli_code` =".$data->cli_code;
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	//funcion encargada de asignar los precios por defecto que tienen los servicios a un cliente
	function addPriceToService($data)
	{
		$sql = "
		INSERT INTO `client_price` (
		`clpr_code` ,
		`ser_code` ,
		`cli_code` ,
		`clpr_price_col` ,
		`clpr_price_us` ,
		`clpr_price_bol`
		)
		VALUES (
		NULL , '".$data->ser_code."', '".$data->cli_code."', '".$data->price_col."', '".$data->price_us."', '".$data->price_bol."'
		);";
		
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	public function listServices()
	{
		$sql = "SELECT ser_code,ser_price,ser_price_us,ser_price_bol FROM services";		
		if($consult = mysql_query($sql)){
	
			$data = array();	
			$i=0;
			while($row=mysql_fetch_array($consult))
			{				
				$data[$i]->ser_code				= $row['ser_code'];			
				
				$data[$i]->ser_price_col		= $row['ser_price'];
				$data[$i]->ser_price_us			= $row['ser_price_us'];
				$data[$i]->ser_price_bol		= $row['ser_price_bol'];			
				
				$i++;
			}
				
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	//Funcion que se encarga de cambiarle estado de liberado a verificacion a un codigo cuando el cliente
	//encuentra que el codigo no puede ser utilizado por el.
	public function verificationCode($code){
		if(isset($code) && $code != '')
		{
			$sql = "UPDATE operator_notice SET libimei_status = 2, libimei_verification=1, libimei_date = '".date("Y-m-d H:i:s")."' WHERE libimei_code = ".(int)$code;
			if(mysql_query($sql))
			   return true;
			else{
				securityMasterUnlock(55,1,$sql);
				return false;
			}   
		}
	}
	
	//funcion encargada de obtener los Creditos que al cliente se le han asignado
	function getAddedBonusClient($clientCode)
	{			
		$sql = "SELECT SUM(bocl_lot) AS cant FROM bonus_client WHERE bocl_type=1 AND bocl_status='Activo' AND cli_code=".$clientCode;
	
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	//funcion encargada de obtener la suma de Creditos restaurados
	function getRepayBonusClient($clientCode)
	{			
		$sql = "SELECT SUM(bocl_lot) AS cant FROM bonus_client WHERE bocl_type=4  AND cli_code=".$clientCode;
	
		if($consulta = mysql_query($sql)) {
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	//funcion encargada de obtener los Creditos que al cliente se le han asignado
	function getPendingBonusClient($clientCode)
	{			
		$sql = "SELECT SUM(bocl_lot) AS cant FROM bonus_client WHERE bocl_type=1 AND bocl_status='Inactivo' AND cli_code=".$clientCode;
	
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	//funcion encargada de obtener los Creditos que el cliente ha usado
	function getUsedBonusClient($clientCode)
	{			
		$sql = "SELECT SUM(bocl_lot) AS cant FROM bonus_client WHERE bocl_type=2 AND cli_code=".$clientCode;
	
		if($consulta = mysql_query($sql)) {
			$row = mysql_fetch_array($consulta);
			return number_format($row['cant'],1,'.','');
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	//funcion encargada de obtener los Creditos que al cliente se le han restado
	function getSubtractedBonusClient($clientCode)
	{			
		$sql = "SELECT SUM(bocl_lot) AS cant FROM bonus_client WHERE bocl_type=3 AND cli_code=".$clientCode;
	
		if($consulta = mysql_query($sql)) {
			$row = mysql_fetch_array($consulta);
			return number_format($row['cant'],1,'.','');
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	//Resta los bonus al cliente, de la tabla clientes
	public function subtractBonusClient($idClient,$bonus){
		$sql = "UPDATE client SET cli_bonus = cli_bonus-".$bonus." WHERE cli_code = ".(int) $idClient;
		if(mysql_query($sql))
		   return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
		return false;
	}
	
	//Suma bonus al cliente, de la tabla clientes
	public function addBonusClient($idClient,$bonus)
	{
		if(isset($idClient) && $idClient != '' && isset($bonus) && $bonus!= '')
		{
			$sql = "UPDATE client SET cli_bonus = cli_bonus+".$bonus." WHERE cli_code = ".$idClient;
			if(mysql_query($sql))
				return true;
			else{
				securityMasterUnlock(55,1,$sql);
				return false;
			}
		}	
		else
			return false;
	}	
	
	///////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener los datos del precio de un servicio de un cliente.
	// Autor: j.h.r
	///////////////////////////////////////////////////////////////////////////////
	public function getDataPriceServicesClient($serCode,$cliCode){
		$sql = "SELECT cp.clpr_price_col, cp.clpr_price_us, cp.clpr_price_bol FROM client_price cp WHERE cp.cli_code=".$cliCode." AND cp.ser_code = ".$serCode;		
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			
			$data->price_col = $row['clpr_price_col'];
			$data->price_bol = $row['clpr_price_bol'];
			$data->price_usd = $row['clpr_price_us'];
			
			return $data;
		}
		else{
				securityMasterUnlock(55,1,$sql);
				return false;
			}
	}
	
	///////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de cancelar una petiion de crédito
	// Autor: sebastianl
	///////////////////////////////////////////////////////////////////////////////
	function cancelCredit($bocl_code)
	{
		$sql = "DELETE FROM `bonus_client` WHERE `bonus_client`.`bocl_code` =".(int)$bocl_code;
		
		if(mysql_query($sql))
		   return true;
		else{
				securityMasterUnlock(55,1,$sql);
				return false;
			}   
	}
	
	//funcion encargada de obtener la cantidad de bobos de un cliente
	function getBonusClient($cli_code)
	{
		$sql = "SELECT cli_bonus FROM client WHERE cli_code=".$cli_code;
		if($consult = mysql_query($sql)) {
			$row = mysql_fetch_array($consult);
			return $row['cli_bonus'];
		}
		else{
				securityMasterUnlock(55,1,$sql);
				return false;
			}
	}
	
	
	////////////////////////////////////////////////////////////////////
	// Esta funcion es la encargada de registrar el uso credito
	// @access public
	///////////////////////////////////////////////////////////////////	
	public function useBonus($data)
	{

		$sql = "INSERT INTO `bonus_client` (
		`bocl_code`,
		`cli_code` ,
		`bocl_date` ,
		`bocl_lot` ,
		`bocl_method_of_payment` ,
		`bocl_money` ,
		`bocl_type` ,
		`bocl_comment` ,
		`bocl_currency`,
		`bocl_references`,
		`bocl_status`,
		`bocl_name`,
		`bocl_last_name`,
		`bocl_bank`,
		`bocl_who`,
		`bocl_mtcn`,
		`bocl_admincomment`,
		`country_name`,
		`city_name`,
		`payment_to`,
		`email_used_for_payment`,
		`file_name`,
		`file_rename`,
		`lib_code`
		)
		VALUES (
		NULL, ".$data->cli_code.", '".date("Y-m-d H:i:s")."', ".$data->bonus.", '".$data->payForm."', ".$data->money.", '2', '".$data->comment."','0', '0','".$data->status."','used','used','used','".$data->who."','0','','','','','','','','".$data->lib_code."')";
	
		if(mysql_query($sql))
			return mysql_insert_id();
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	////////////////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener los datos del servicio al que se le cambia el precio.
	// Autor: J.C.M
	///////////////////////////////////////////////////////////////////////////////////////
	public function getDataService($serCode)
	{	

		$sql = "
		SELECT ser_code,cli_code FROM `client_price` 
		WHERE clpr_code=".$serCode;
		
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			
			$data->service = $row['ser_code'];
			
			//necesitamos saber el nombre del servicio
			$sql2="SELECT ser_name FROM services WHERE ser_code = ".$data->service;
			$consult2  = mysql_query($sql2);		
			$row2      = mysql_fetch_array($consult2);
			/////////////////////////////////////////			
			$data->services_name         = $row2['ser_name'];
			
			$data->cliente = $row['cli_code'];
			
			//necesitamos saber los datos del cliente para el email
			$sql3="SELECT cli_name,cli_last_name,cli_email,cli_payment_form,cli_language_email FROM client WHERE cli_code = ".$data->cliente;
			$consult3  = mysql_query($sql3);		
			$row3      = mysql_fetch_array($consult3);
			/////////////////////////////////////////			
			
			$data->cli_name         	= $row3['cli_name'];
			$data->cli_last_name    	= $row3['cli_last_name'];
			$data->cli_email       		= $row3['cli_email'];
			$data->cli_payment_form 	= $row3['cli_payment_form'];
			$data->cli_language_email 	= $row3['cli_language_email'];
		
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
			}
	}
	
			/*
	 * Esta funcion es la encargada de obtener los datos de una imagen.
	 * @access public
	 * @param $code --> Codigo de la imagen
	 */	
	public function verifiedCode($code)
	{
		$sql = "SELECT libimei_verification  FROM `operator_notice` WHERE libimei_code = ".(int)$code;
	 	if($consulta 	= mysql_query($sql)){
			$row		= mysql_fetch_array($consulta);
			return $row['libimei_verification'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	//////////////////////////////////////////////////////////////////////////////////////////////////////////
	//funcion encargada de obtener el idioma de email de determinado cliente el cual es buscado por su email
	//////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	
	function getDataClientEmail($email)
	{
		$sql = "SELECT cli_language_email,cli_name,cli_last_name FROM client WHERE cli_email='".$email."'";
		if($consult = mysql_query($sql)){
			$row = mysql_fetch_array($consult);
			
			$data->cli_language_email 	= $row['cli_language_email'];
			$data->name				 	= $row['cli_name'];
			$data->lastName			 	= $row['cli_last_name'];
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	//////////////////////////////////////////////////////////////////////////////////////////////////////////
	//imeiExist->funcion encargada de mirar si un imei cualquiera ya esta en la B.D. 
	//@author:J.C.M
	//////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	public function imeiExist($imei){
		$sql = "
		SELECT count(*) AS cant
		FROM liberations l JOIN operator_notice li ON l.lib_code=li.lib_code
		WHERE (li.libimei_number = '".$imei."' OR l.lib_imei='".$imei."') 
		AND li.libimei_number IS NOT NULL AND (li.libimei_status=0 OR li.libimei_status=2 OR li.libimei_status=4)";

		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	
	///////////////////////////////////////////////////////////////////////////////
	// getLiberationPending: Cuenta cuantas liberaciones tiene pendientes 
	// Autor: J.H.R
	///////////////////////////////////////////////////////////////////////////////
	public function getLiberationPending($cli_code)
	{
		$sql = "
		SELECT count(*) AS cant
		FROM liberations AS libe, operator_notice AS libei 
		WHERE libe.lib_code = libei.lib_code
		AND libe.cli_code=".$cli_code.' AND (libimei_status=0 OR libimei_status=2) GROUP BY cli_code';
		
		if($consulta 	= mysql_query($sql)){
			$row 		= mysql_fetch_array($consulta);			
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener por servicio la cantidad de liberaciones.
	/////////////////////////////////////////////////////////////////////////////////
	function getLiberationsGroupService($inicio = 0,$cantidad = 0,$search='',$order_by='' , $from_date='' , $to_date = '')
	{
		$where = '';
		
		if($search != '')
			$where .= " AND (ser_name like '%".$search."%')";
		
		if($from_date != '')
			$where .= " AND (lb.lib_date >= '".$from_date." 00:00:00') ";
	
		if($to_date != '')
			$where .= " AND (lb.lib_date <= '".$to_date." 23:59:59') ";						
		
		if($order_by=='')
			$order_by = " ORDER BY t1.amount DESC ";
		
		$sql  = " SELECT s.ser_name, s.img_rename, t1.amount ";
		$sql .= " FROM ";
		$sql .= " (SELECT lb.ser_code, count(*) AS amount ";
		$sql .= " FROM liberations lb ";
		$sql .= " JOIN ";
		$sql .= " operator_notice li ";
		$sql .= " ON lb.lib_code = li.lib_code WHERE li.libimei_status=1 ".$where;
		$sql .= " GROUP BY lb.ser_code) t1 ";
		$sql .= " JOIN services s WHERE t1.ser_code=s.ser_code ".$order_by;
		$sql .= " LIMIT ".$inicio.",".$cantidad;
		
		if($consult  = mysql_query($sql)){	
			$data = array();	
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->ser_name		= $row['ser_name'];
				$data[$i]->img_rename	= $row['img_rename'];
				$data[$i]->amount 		= $row['amount'];
				
				$i++;				
			}
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener contar por servicio la cantidad de liberaciones.
	/////////////////////////////////////////////////////////////////////////////////
	function countLiberationsGroupService($search='', $from_date='' , $to_date = '')
	{
		$where = '';
		
		if($search != '')
			$where .= " AND (ser_name like '%".$search."%')";
		
		if($from_date != '')
			$where .= " AND (lb.lib_date >= '".$from_date." 00:00:00') ";
	
		if($to_date != '')
			$where .= " AND (lb.lib_date <= '".$to_date." 23:59:59') ";
		
		$sql  = " SELECT count(*) as cant ";
		$sql .= " FROM ";
		$sql .= " (SELECT lb.ser_code, count(*) AS amount ";
		$sql .= " FROM liberations lb ";
		$sql .= " JOIN ";
		$sql .= " operator_notice li ";
		$sql .= " ON lb.lib_code = li.lib_code WHERE li.libimei_status=1 ".$where;
		$sql .= " GROUP BY lb.ser_code) t1 ";
		$sql .= " JOIN services s WHERE t1.ser_code=s.ser_code ";

		if($consult  = mysql_query($sql)){	
			$row = mysql_fetch_array($consult);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener por moneda la cantidad de creditos consumidos.
	/////////////////////////////////////////////////////////////////////////////////
	function getLiberationsGroupCurrency($inicio = 0,$cantidad = 0,$order_by='' , $from_date='' , $to_date = '')
	{
		$where = '';
		
		if($from_date != '')
			$where .= " AND (libimei_date_liberation >= '".$from_date." 00:00:00') ";
	
		if($to_date != '')
			$where .= " AND (libimei_date_liberation <= '".$to_date." 23:59:59') ";						
		
		if($order_by=='')
			$order_by = " ORDER BY credits DESC ";
				
		$sql  = " SELECT sum(libimei_bonus) as credits, libimei_money ";
		$sql .= " FROM `operator_notice` WHERE libimei_status=1 ".$where;
		$sql .= " GROUP BY libimei_money ".$order_by;
		$sql .= " LIMIT ".$inicio.",".$cantidad;

		if($consult  = mysql_query($sql)){	
			$data = array();	
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{
				$data[$i]->credits		 = $row['credits'];
				$data[$i]->libimei_money = $row['libimei_money'];
	
				$i++;				
			}
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/////////////////////////////////////////////////////////////////////////////////
	// Funcion encargada de obtener el email con sus case sensative como primer filtro de loguin.
	/////////////////////////////////////////////////////////////////////////////////
	public function validateEmailAccount($email){
		$sql = "SELECT cli_email FROM client WHERE `cli_email` = '".$email."'";		
		if($consulta = mysql_query($sql)){
			if($row = mysql_fetch_array($consulta))
				return $row['cli_email'];
			else
				return 'ERROR';
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/**
	*	Función responsable de obtener la información personal y de los movimientos en cuanto a créditos se refiere.
	*	@autor: jsl.
	*	@date: 12 AGO 2011
	*/
	function getClientInformation($cli_code){
		$sql = "
		SELECT c.cli_code, c.cli_name, c.cli_last_name, c.cli_email, c.cli_language_email, c.cli_rename, c.cli_payment_form, c.cli_type, IFNULL(t6.purchased_credit,0) AS purchased_credit, IFNULL(t6.used_credit,0) AS
		used_credit, IFNULL(t6.substracted_credit,0) AS substracted_credit, IFNULL(t6.refunded_credit,0) AS refunded_credit, t6.avaliable_credit FROM
		client c LEFT JOIN
			(SELECT t4.cli_code, t4.purchased_credit, t4.used_credit, t4.substracted_credit, t5.refunded_credit,
			 									(IFNULL(t4.purchased_credit,0)+IFNULL(t5.refunded_credit,0)-IFNULL(t4.substracted_credit,0)-IFNULL(t4.used_credit,0)) AS avaliable_credit FROM
			(SELECT t2.cli_code, t2.purchased_credit, t2.used_credit, t3.substracted_credit FROM
			(	SELECT t0.cli_code, t0.purchased_credit, t1.used_credit FROM
				(SELECT bc.cli_code, SUM(bc.bocl_lot) AS purchased_credit FROM bonus_client bc WHERE bc.bocl_type=1 AND bc.bocl_status='Activo' GROUP BY bc.cli_code) t0				
				LEFT JOIN
				(SELECT bc.cli_code, SUM(bc.bocl_lot) AS used_credit FROM bonus_client bc WHERE bc.bocl_type=2 GROUP BY bc.cli_code) t1
				ON t0.cli_code=t1.cli_code  )t2
			LEFT JOIN
			(SELECT bc.cli_code, SUM(bc.bocl_lot) AS substracted_credit FROM bonus_client bc WHERE bc.bocl_type=3 GROUP BY bc.cli_code) t3
			ON t2.cli_code=t3.cli_code) t4
			LEFT JOIN
			(SELECT bc.cli_code, SUM(bc.bocl_lot) AS refunded_credit FROM bonus_client bc WHERE bc.bocl_type=4 GROUP BY bc.cli_code) t5
			ON t4.cli_code=t5.cli_code) t6
		ON c.cli_code=t6.cli_code WHERE c.cli_code=".$cli_code;		
		
		if(!($init==0 && $amount==0))
			$sql .= " LIMIT ".$init.",".$amount.";";
		
		if($result = mysql_query($sql)){
			$row = mysql_fetch_array($result);
		
			$data->cli_code = $row["cli_code"];
			$data->cli_name = $row["cli_name"];
			$data->cli_last_name = $row["cli_last_name"];
			$data->cli_email = $row["cli_email"];
			$data->cli_rename = $row["cli_rename"];
			$data->cli_payment_form = $row["cli_payment_form"];
			$data->cli_type = $row["cli_type"];
			$data->purchased_credit = $row["purchased_credit"];
			$data->used_credit = $row["used_credit"];
			$data->substracted_credit = $row["substracted_credit"];
			$data->refunded_credit = $row["refunded_credit"];
			$data->avaliable_credit = $row["avaliable_credit"];
			$data->language_email = $row["cli_language_email"];
			
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/**
	*	Función responsable de obtener la API y el codigo de liberacion en la API si es que la liberacion se realizo por este medio
	*	@autor: JSL
	*	@date: 12 DIC 2011
	*/
	function getOrderIdUnlock($libimei_code){
		$sql = "SELECT l.API_provider_code, l.API_order_id FROM operator_notice l WHERE l.libimei_code = ".(int) $libimei_code." AND l.API_provider_code > 0";
		$result = mysql_query($sql);
		$row = mysql_fetch_array($result);
		
		$data->API_provider_code = $row["API_provider_code"];
		$data->API_order_id = $row["API_order_id"];
		
		return $data;
	}
	
	/**
	*	Funcion responsable de editar la informacion de un pago cuando el pago ha sido efectuado con éxito en paypal.
	*	@autor: JSL
	*	@date: 06/01/12
	*/
	function paymentPaypalSuccessfull($data){
		$sql = "UPDATE bonus_client SET bocl_type='1', bocl_status='Activo', email_used_for_payment='".$data->email_used_for_payment."', txn_id='".$data->txn_id."'
				WHERE bocl_code=".(int) $data->bocl_code.";";
		if(mysql_query($sql))
			return true;
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/**
	*	Funcion responsable de verificar si existe un txn_id relacionado con una solicitud de credito.
	*	@autor: JSL
	*	@date: 06/01/12
	*/
	function issetTxn_id($txn_id){
		$sql = "SELECT COUNT(*) as cant FROM bonus_client bc WHERE bc.txn_id='".$txn_id."';";
		if($result = mysql_query($sql)){
			$row = mysql_fetch_array($result);
			return $row["cant"];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	/**
	*	Funcion responsable de obtener la url de de un reseller teniendo por parametro el codigo de una loberacion.
	*	@autor: JSL.
	*	@date: 07/MAR/2012
	*/
	public function getURLResellerByImei($libimei_code){
		$sql = "
		SELECT cr.conf_db_url FROM configuration_reseller cr JOIN
		(SELECT lir.reseller_code FROM liberation_imei_reseller lir WHERE lir.libimei_code=".$libimei_code.") t
		ON cr.cli_code=t.reseller_code; ";
		
		if ( $result = mysql_query($sql) ){
			if ( $row = mysql_fetch_array ( $result ) )			
				return $row['conf_db_url'];
			else
				return NULL;
		}
		else
			return NULL;		
	}
	
	
	/*Reporte para mirar los bunos de cada cliente en la tabla cliente y lel saldo real para ver si tenemos diferencias que no deben haber */
	public function reportBonusClient($cli_code = '')
	{
		$where = '';
		if(isset($cli_code) && $cli_code != '')
			$where = ' AND cli_code = '.$cli_code;
		$sql = "SELECT cli_code, cli_name, cli_last_name, cli_email, cli_payment_form, cli_bonus FROM client WHERE cli_status = 1".$where;	
		
		if($consult = mysql_query($sql))
		{
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{				
				
				$sql2 = "SELECT bocl_type, bocl_status, bocl_lot FROM bonus_client WHERE cli_code = ".$row['cli_code'];	
				
				$pesos = 0;
				if($consult2  = mysql_query($sql2))
				{
					while($row2 = mysql_fetch_array($consult2))
					{						
						switch($row2['bocl_type'])
						{
							case 1://Entrada de Creditos por el admin o poruq el cliente los pide
								if($row2['bocl_status']!='Inactivo' && $row2['bocl_status']!='Denegado')
									$pesos = $pesos + $row2['bocl_lot'];	
							break;
								
							case 2:
								$pesos = $pesos - $row2['bocl_lot'];	
							break;
							
							case 3:
								$pesos = $pesos - $row2['bocl_lot'];
							break;
							
							case 4:
								$pesos = $pesos + $row2['bocl_lot'];
							break;						
						}	
					}	
				}
				
				if($row['cli_bonus'] != $pesos)
				{
					$data[$i]->cli_code 		= $row['cli_code'];
					$data[$i]->cli_name 		= $row['cli_name'];
					$data[$i]->cli_last_name 	= $row['cli_last_name'];
					$data[$i]->cli_email 		= $row['cli_email'];
					$data[$i]->cli_payment_form = $row['cli_payment_form'];
					$data[$i]->cli_bonus 		= $row['cli_bonus'];
					$data[$i]->pesos 			= $pesos;
					$data[$i]->diferencia = ($pesos-$row['cli_bonus']);		
					$i++;
				}
			}	
				
		}
		return $data;
	}
	
	function validateClientData($code, $email, $bonus)
	{
		$sql = "SELECT COUNT(*) as cant FROM client c WHERE cli_code=".$code." AND cli_email='".$email."' ;";
		if($result = mysql_query($sql))
		{
			$row = mysql_fetch_array($result);
			$cant = $row["cant"];
		
			if($cant>0)
				return true;
			else
				return false;	
		}
		else
		{
			//securityMasterUnlock(55,1,$sql);
			return false;
		}
		
	}
	
	//////////////////////////////////////////////////////////////////////////////////////////////////////////
	//imeiExist->funcion encargada de mirar si un imei cualquiera ya esta en la B.D. 
	//@author:J.C.M
	//////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	public function imeiBlackList($imei){
		
		$sql = "SELECT count(*) AS cant FROM black_list WHERE bl_imei = '".$imei."'";

		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	public function listCountryPublic()
	{
		$sql = "SELECT id,pais FROM paises";
				
		if($consult = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row=mysql_fetch_array($consult))
			{
				$data[$i]->cou_code		= $row['id'];			
				
				$data[$i]->cou_name		= $row['pais'];
				
				$i++;
			}
			return $data;
		}
		else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}
	}
	
	//////////////////////////////////////////////////////////////////////////////////
	// editClient      : Esta funcion es la encargada de editar los datos de un client
	// parameter     : $new_data -> es el array con los datos a editar
	//////////////////////////////////////////////////////////////////////////////////
	function updatePriceReseller($cli_code,$ser_code,$price_col,$price_us,$price_bol)
	{
		$sql=
		"UPDATE `client_price` SET 
		`clpr_price_col`     = '".$price_col."',
		`clpr_price_us`     = '".$price_us."',
		`clpr_price_bol`   = '".$price_bol."'
		 WHERE `cli_code` =".$cli_code." AND ser_code = ".$ser_code.";";
	//die($sql);
	if(mysql_query($sql))
	   		return true;
	else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	
	function getCodeImei($lib_code)
	{
		$sql = "SELECT libimei_code FROM `operator_notice` WHERE `lib_code` = ".$lib_code;
		if($result = mysql_query($sql))
		{
			$row = mysql_fetch_array($result);
			//$code = $row["libimei_code"];
		
			return $row["libimei_code"];
		}
		else
		{
			return false;
		}
	}
	
	public function listaClienteMalos($payment)
	{
		$sql = "SELECT cli_code,cli_name FROM client WHERE cli_payment_form='".$payment."'";
				
		if($consult = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row=mysql_fetch_array($consult))
			{			
				$data[$i]->cli_code	= $row['cli_code'];
				$data[$i]->cli_name	= $row['cli_name'];
				
				$i++;
			}
			return $data;
		}
		else{
			return false;
		}
	}
	
	public function listaTodosServicios()
	{
		$sql = "SELECT ser_code,ser_name,ser_price,ser_price_us,ser_price_bol FROM services";
				
		if($consult = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row=mysql_fetch_array($consult))
			{			
				$data[$i]->ser_code	= $row['ser_code'];
				$data[$i]->ser_name	= $row['ser_name'];
				$data[$i]->ser_price_col = $row['ser_price'];
				$data[$i]->ser_price_us = $row['ser_price_us'];
				$data[$i]->ser_price_bol = $row['ser_price_bol'];
				$i++;
			}
			return $data;
		}
		else{
			return false;
		}
	}
	
	public function errorPrecio($cli_code,$ser_code,$precio,$payment_form)
	{
		$sql = "SELECT count(*) AS cant FROM client_price WHERE cli_code=".$cli_code." AND ser_code=".$ser_code." AND clpr_price_".$payment_form." > ".$precio ;
		if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			return $row['cant'];
		}
		else{
			return false;
		}
	}
	
	function actualizaPrecioErroneo($cli_code,$ser_code,$precio,$payment_form)
	{
		$sql=
		"UPDATE `client_price` SET `clpr_price_".$payment_form."` = '".$precio."' WHERE `cli_code` =".$cli_code." AND ser_code = ".$ser_code.";";
	//die($sql);
	if(mysql_query($sql))
	   		return true;
	else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	public function obtenerServicio($ser_code)
	{
		$sql = "SELECT ser_code,ser_name,ser_price,ser_price_us,ser_price_bol FROM services WHERE ser_code =".$ser_code;
				
		if($result = mysql_query($sql))
		{
			$row = mysql_fetch_array($result);			
			
			$data->ser_code	= $row['ser_code'];
			$data->ser_name	= $row['ser_name'];
			$data->ser_price_col = $row['ser_price'];
			$data->ser_price_us = $row['ser_price_us'];
			$data->ser_price_bol = $row['ser_price_bol'];
			$i++;
			
			if($data->ser_code != '')
				return $data;
			else
				return false;
		}
		else{
			return false;
		}
	}
	
	public function listaTodosClientesColombianos()
	{
		$sql = "SELECT c.clpr_code, s.ser_price FROM  `services` s JOIN client_price c on(c.ser_code=s.ser_code)  JOIN  client cl using(cli_code) WHERE s.ser_price < c.clpr_price_col and cl.cli_payment_form = 'Pesos Colombianos'";
				
		if($consult = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row=mysql_fetch_array($consult))
			{			
				$data[$i]->clpr_code	= $row['clpr_code'];
				$data[$i]->ser_price_col = $row['ser_price'];
				$i++;
			}
			return $data;
		}
		else{
			return false;
		}
	}
	
	function actualizaPrecioMaloColombianos($clpr_code,$precio)
	{
		$sql=
		"UPDATE `client_price` SET `clpr_price_col` = '".$precio."' WHERE `clpr_code` =".$clpr_code;
	//die($sql);
	if(mysql_query($sql))
	   		return true;
	else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	public function listaTodosClientesBolivares()
	{
		$sql = "SELECT c.clpr_code, s.ser_price_bol FROM  `services` s JOIN client_price c on(c.ser_code=s.ser_code)  JOIN  client cl using(cli_code) WHERE s.ser_price_bol < c.clpr_price_bol and cl.cli_payment_form = 'Bolivares'";
				
		if($consult = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row=mysql_fetch_array($consult))
			{			
				$data[$i]->clpr_code	= $row['clpr_code'];
				$data[$i]->ser_price_bol = $row['ser_price_bol'];
				$i++;
			}
			return $data;
		}
		else{
			return false;
		}
	}
	
	function actualizaPrecioMaloBolivares($clpr_code,$precio)
	{
		$sql=
		"UPDATE `client_price` SET `clpr_price_bol` = '".$precio."' WHERE `clpr_code` =".$clpr_code;
	//die($sql);
	if(mysql_query($sql))
	   		return true;
	else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
	public function listaTodosClientesDolares()
	{
		$sql = "SELECT c.clpr_code, s.ser_price_us FROM  `services` s JOIN client_price c on(c.ser_code=s.ser_code)  JOIN  client cl using(cli_code) WHERE s.ser_price_us < c.clpr_price_us and cl.cli_payment_form = 'Dolares'";
				
		if($consult = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row=mysql_fetch_array($consult))
			{
				$data[$i]->clpr_code	= $row['clpr_code'];
				$data[$i]->ser_price_us = $row['ser_price_us'];
				$i++;
			}
			return $data;
		}
		else{
			return false;
		}
	}
	
	function actualizaPrecioMaloDolares($clpr_code,$precio)
	{
		$sql=
		"UPDATE `client_price` SET `clpr_price_us` = '".$precio."' WHERE `clpr_code` =".$clpr_code;
	//die($sql);
	if(mysql_query($sql))
	   		return true;
	else{
			securityMasterUnlock(55,1,$sql);
			return false;
		}   
	}
	
}
?>